SAK }‘ŠÙ

SAK Streets ƒƒCƒ“ƒXƒgƒŠ[ƒg }‘ŠÙEƒJƒtƒF ˆê—— SAK Streets ŒŸõ
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | ƒtƒŠ[ƒ\ƒtƒg |

SQL –â‚¢‡‚킹 - •ªÍƒtƒ@ƒ“ƒNƒVƒ‡ƒ“AoverApartition byArangeAdense_rank

•ªÍƒtƒ@ƒ“ƒNƒVƒ‡ƒ“‚ÌŽg‚¢•û‚ª—Ç‚­•ª‚©‚ç‚È‚¢‚Ì‚ÅŽŽ‚µ‚Ä‚Ý‚½B
‚Ù‚Æ‚ñ‚ǂ̏WŒvŠÖ”‚ª•ªÍŠÖ”‚Æ‚µ‚ÄŽg‚¦‚é‚Ý‚½‚¢‚Å‚ ‚éB
‚±‚ê‚Í Oracle8i ‚©‚ç‚ ‚Á‚½‹@”\‚È‚ñ‚¾‚낤‚©B

¡PARTITION BY

  EƒOƒ‹[ƒvWŒvŠî–{Œ^B

select •i”Ô, count(*), sum(Žó’”), avg(Žó’”) from Žó’m group by •i”Ô;

•i”Ô                 COUNT(*) SUM(Žó’”) AVG(Žó’”)
------------------ ---------- ----------- -----------
a001                       25       10562      422.48
b001                        7       16797  2399.57143
c001                        3        6010  2003.33333

  E‘WŒvŠî–{Œ^B

select count(*), sum(Žó’”), avg(Žó’”) from Žó’m;

  COUNT(*) SUM(Žó’”) AVG(Žó’”)
---------- ----------- -----------
        35       33369       953.4

  E•i”Ô’PˆÊ‚Ì•½‹ÏŽó’”‚𖾍ׂɒljÁB
    partition by ‚́AƒOƒ‹[ƒv‰»•ªŠ„‚·‚éB

select Žó’”ԍ†, •i”Ô, Žó’”,
  avg(Žó’”) over (partition by •i”Ô order by •i”Ô) aŽó’”
  from Žó’m order by •i”Ô, Žó’”ԍ†
;

Žó’”ԍ†   •i”Ô                   Žó’”    AŽó’”
---------- ------------------ ---------- ----------
000001-001 a001                       70     422.48
000001-002 a001                      500     422.48
000001-056 a001                      500     422.48
...

  Epartition by ‚ª‚È‚¢‚Æ‘WŒv‚É‚È‚éB

select Žó’”ԍ†, •i”Ô, Žó’”,
  avg(Žó’”) over () aŽó’”
  from Žó’m order by •i”Ô, Žó’”ԍ†
;

Žó’”ԍ†   •i”Ô                   Žó’”    AŽó’”
---------- ------------------ ---------- ----------
000001-001 a001                       70      953.4
000001-002 a001                      500      953.4
000001-056 a001                      500      953.4
...

¡RANGEABETWEEN

  E•i”Ô’PˆÊ‚ÌŒ”‚𖾍ׂɒljÁB
    partition by ‚́AƒOƒ‹[ƒv‰»•ªŠ„‚·‚éB

select Žó’”ԍ†, •i”Ô, Žó’”,
  count(*) over (partition by •i”Ô order by •i”Ô) ct
  from Žó’m order by •i”Ô, Žó’”ԍ†
;

Žó’”ԍ†   •i”Ô                   Žó’”         CT
---------- ------------------ ---------- ----------
000001-001 a001                       70         25
000001-002 a001                      500         25
000001-056 a001                      500         25
...

  EŽó’”‚æ‚è -1000 ` +4500 ‚Ì•i”Ô–ˆ‚ÌŒ”‚𖾍ׂɒljÁB

select Žó’”ԍ†, •i”Ô, Žó’”,
  count(*) over (partition by •i”Ô order by Žó’”
    range between 1000 preceding and 4500 following) ct
  from Žó’m order by •i”Ô, Žó’”ԍ†
;

Žó’”ԍ†   •i”Ô                   Žó’”         CT
---------- ------------------ ---------- ----------
000001-061 c001                     1500          2
000921-001 c001                       10          3
100011-002 c001                     4500          1
...

¡DENSE_RANKARANK

  EŽó’”‚Ì‘½‚¢‡‚Ƀ‰ƒ“ƒN‚ð•t‚¯‚éB

select Žó’”ԍ†, •i”Ô, Žó’”,
  dense_rank() over (order by Žó’” desc) rank
  from Žó’m order by rank, •i”Ô, Žó’”ԍ†
;

Žó’”ԍ†   •i”Ô                   Žó’”       RANK
---------- ------------------ ---------- ----------
000011-001 b001                     6000          1
000019-001 b001                     5555          2
100011-002 c001                     4500          3
...

  Erank ‚Í“¯‚¶ƒ‰ƒ“ƒN‚ª‚ ‚é‚ƃ‰ƒ“ƒN’l‚ª”ò‚ԁB

select Žó’”ԍ†, •i”Ô, Žó’”,
  rank() over (order by Žó’” desc) rank
  from Žó’m order by rank, •i”Ô, Žó’”ԍ†
;

Žó’”ԍ†   •i”Ô                   Žó’”       RANK
---------- ------------------ ---------- ----------
000011-001 b001                     6000          1
000019-001 b001                     5555          2
100011-002 c001                     4500          3
...

  E—á‚͈«‚¢‚ªAŽd“üæCD ‚̍ŏ‰‚̃‰ƒ“ƒN‚ɑ΂·‚éÅ¬Žó’”‚Æ
    Žd“üæCD ‚̍Ōã‚̃‰ƒ“ƒN‚ɑ΂·‚éÅ‘åŽó’”‚ð‹‚ß‚éB

select •i”Ô,
  min(Žó’”) keep (dense_rank first order by “¾ˆÓæCD) min,
  max(Žó’”) keep (dense_rank last order by “¾ˆÓæCD) max
  from Žó’m group by •i”Ô
;

•i”Ô                      MIN        MAX
------------------ ---------- ----------
a001                       10       4000
b001                     2000       6000
c001                       10       1500
...

  E“¯‚¶‚­–¾×ƒŒƒxƒ‹‚ōs‚¤B

select Žó’”ԍ†, •i”Ô,
  min(Žó’”) keep (dense_rank first order by “¾ˆÓæCD)
    over(partition by •i”Ô) min,
  max(Žó’”) keep (dense_rank last order by “¾ˆÓæCD)
    over(partition by •i”Ô) max
  from Žó’m order by •i”Ô
;

Žó’”ԍ†   •i”Ô                      MIN        MAX
---------- ------------------ ---------- ----------
000001-002 a001                       10       4000
000001-001 a001                       10       4000
000001-056 a001                       10       4000
...

¡SQL Šî‘bŽÀ’n•ÒŽ‘—¿
¡SQL Šî‘b•ÒŽ‘—¿
¡SQL ƒ`ƒ…[ƒjƒ“ƒO•ÒŽ‘—¿
¡Oracle PL/SQL •ÒŽ‘—¿
¡PostgreSQL •ÒAJAVA ServletAJSP •ÒŽ‘—¿
¡MySQL •ÒŽ‘—¿
| VB | SQL | Perl | PHP | ASP | JSP | C | PC | HP | FAQ | ƒtƒŠ[ƒ\ƒtƒg |

SAK Streets ƒƒCƒ“ƒXƒgƒŠ[ƒg }‘ŠÙEƒJƒtƒF ˆê—— SAK Streets ŒŸõ



Copyright (C) 1999-2017 Y.SAK