λ³Έλ¬Έ λ°”λ‘œκ°€κΈ°

Database

SQL λˆ„μ  합계 ν•¨μˆ˜: SUM OVER

λ¨Όμ € μ•Œμ•„κ°€κΈ°!

 

  • 짙은 μ΄ˆλ‘μƒ‰μœΌλ‘œ μž‘μ„± 된 뢀뢄은 μƒλž΅ κ°€λŠ₯ν•œ λΆ€λΆ„μž…λ‹ˆλ‹€.
  • νŠΉμ • 속성 λ³„λ‘œ κ΅¬λΆ„ν•˜κ³ μž ν•  λ•Œ(=GROUP BY 와 같은 λ™μž‘μ„ μ‹œν‚€κ³  싢을 λ•Œ) PARTITION BYλ₯Ό μ‚¬μš©ν•©λ‹ˆλ‹€.

 


 

 

SUM OVER

λˆ„μ  합계λ₯Ό κ΅¬ν•©λ‹ˆλ‹€.

sum(λŒ€μƒ) over (partition by λŒ€μƒ, order by λŒ€μƒ)

 

 

μ˜ˆμ‹œ 1 ) PANAME ν…Œμ΄λΈ”μ„ μ‘°νšŒν•˜μ—¬ μ œν’ˆμ½”λ“œ, 판맀점, νŒλ§€λ‚ μ§œ, νŒλ§€λŸ‰, νŒλ§€κΈˆμ•‘κ³Ό μ œν’ˆ μ½”λ“œ 별 각 μŠ€ν† μ–΄μ˜ 합계 좜λ ₯

 

select P_CODE,
       P_STORE,
       P_DATE,
       P_QTY,
       P_TOTAL,
       sum(P_TOTAL) over (partition by P_CODE, P_STORE order by P_DATE, P_TOTAL) "TOTAL"
from PANMAE;

 

μ˜ˆμ‹œ 2 ) 판맀 λΉ„μœ¨ κ΅¬ν•˜κΈ°

 

select P_CODE, P_STORE, P_QTY, sum(P_QTY) over ( ) "TOTAL", round((P_QTY / sum(P_QTY) over ( )) * 100, 2) "QTY_%"
from PANMAE
where P_CODE = 100;

-- * * OR * *

select P_CODE,
       P_STORE,
       P_QTY,
       sum(P_QTY) over ( )                                 "TOTAL",
       round(ratio_to_report(sum(P_QTY)) over () * 100, 2) "QTY_%2"
from PANMAE
where P_CODE = 100
group by P_CODE, P_STORE, P_QTY;