{"id":1676,"date":"2024-02-01T11:54:02","date_gmt":"2024-02-01T02:54:02","guid":{"rendered":"https:\/\/dohokin.com\/?p=1676"},"modified":"2024-02-01T11:54:02","modified_gmt":"2024-02-01T02:54:02","slug":"sql-over","status":"publish","type":"post","link":"https:\/\/dohokin.com\/?p=1676","title":{"rendered":"SQL OVER"},"content":{"rendered":"\n
\"\"<\/figure>\n\n\n\n

OVER<\/code> \uc808\uc740 SQL\uc758 \uc708\ub3c4\uc6b0 \ud568\uc218\uc640 \ud568\uaed8 \uc0ac\uc6a9\ub418\uba70, \ud574\ub2f9 \ud568\uc218\uac00 \uc801\uc6a9\ub420 \ub370\uc774\ud130 \uc9d1\ud569\uc758 \ubc94\uc704(\uc708\ub3c4\uc6b0)\ub97c \uc9c0\uc815\ud558\ub294 \ub370 \ud544\uc694\ud569\ub2c8\ub2e4. ROW_NUMBER()<\/code>, RANK()<\/code>, DENSE_RANK()<\/code>, LEAD()<\/code>, LAG()<\/code> \ub4f1\uc758 \uc708\ub3c4\uc6b0 \ud568\uc218\uc5d0\uc11c OVER<\/code> \uc808\uc740 \ud544\uc218\uc801\uc785\ub2c8\ub2e4. \uc708\ub3c4\uc6b0 \ud568\uc218\ub97c \uc0ac\uc6a9\ud558\uba74, \uac01 \ud589\uc5d0 \ub300\ud574 \uacc4\uc0b0\uc744 \uc218\ud589\ud560 \ub54c \uc9c0\uc815\ub41c \ubc94\uc704\uc758 \ub2e4\ub978 \ud589\ub4e4\uc744 \ucc38\uc870\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n

OVER<\/code> \uc808 \uc5c6\uc774 \uc708\ub3c4\uc6b0 \ud568\uc218\ub97c \uc0ac\uc6a9\ud558\ub824\uace0 \ud558\uba74, SQL \ucffc\ub9ac\ub294 \uc624\ub958\ub97c \ubc1c\uc0dd\uc2dc\ud0b5\ub2c8\ub2e4. OVER<\/code> \uc808\uc740 \uc708\ub3c4\uc6b0 \ud568\uc218\uac00 \uc5b4\ub5a4 \ubc29\uc2dd\uc73c\ub85c \ub370\uc774\ud130\ub97c \ubd84\ud560(partitioning), \uc815\ub82c(ordering), \ub610\ub294 \ubc94\uc704\ub97c \uc81c\ud55c(frame specification)\ud560\uc9c0 \uc815\uc758\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n

\uc608\ub97c \ub4e4\uc5b4, ROW_NUMBER() OVER (ORDER BY age DESC)<\/code>\uc5d0\uc11c OVER<\/code> \uc808\uc740 ROW_NUMBER()<\/code> \ud568\uc218\uac00 age<\/code> \uceec\ub7fc\uc758 \uac12\uc5d0 \ub530\ub77c \ub0b4\ub9bc\ucc28\uc21c\uc73c\ub85c \ub370\uc774\ud130\ub97c \uc815\ub82c\ud558\uace0, \uc774 \uc21c\uc11c\uc5d0 \ub530\ub77c \uac01 \ud589\uc5d0 \uace0\uc720\ud55c \uc21c\uc11c \ubc88\ud638\ub97c \ubd80\uc5ec\ud558\ub3c4\ub85d \uc9c0\uc2dc\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n

\ub530\ub77c\uc11c, \uc708\ub3c4\uc6b0 \ud568\uc218\ub97c \uc0ac\uc6a9\ud558\uae30 \uc704\ud574\uc11c\ub294 OVER<\/code> \uc808\uc774 \ubc18\ub4dc\uc2dc \ud544\uc694\ud558\uba70, \uc774\ub97c \uc0dd\ub7b5\ud560 \uc218 \uc5c6\uc2b5\ub2c8\ub2e4. OVER<\/code> \uc808\uc744 \uc0dd\ub7b5\ud558\uba74, \ud568\uc218\uac00 \uc801\uc6a9\ub420 \ub370\uc774\ud130 \uc9d1\ud569\uc758 \ubc94\uc704\uac00 \uba85\ud655\ud558\uc9c0 \uc54a\uac8c \ub418\uc5b4, SQL \uc5d4\uc9c4\uc774 \ud574\ub2f9 \ud568\uc218\ub97c \uc62c\ubc14\ub974\uac8c \ucc98\ub9ac\ud560 \uc218 \uc5c6\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n

\n\n\n\n

OVER<\/code> \uc808\uc744 \uc0ac\uc6a9\ud558\ub294 SQL \uc708\ub3c4\uc6b0 \ud568\uc218\uc758 \uc608\uc81c\ub97c \uba87 \uac00\uc9c0 \uc81c\uacf5\ud558\uaca0\uc2b5\ub2c8\ub2e4. \uc774 \uc608\uc81c\ub4e4\uc740 \ub370\uc774\ud130 \ubd84\uc11d\uacfc \ubcf4\uace0\uc11c \uc791\uc131\uc5d0 \uc790\uc8fc \uc0ac\uc6a9\ub418\uba70, \ub370\uc774\ud130\ubca0\uc774\uc2a4 \ub0b4\uc5d0\uc11c \ub370\uc774\ud130\uc758 \uc21c\uc704\ub97c \ub9e4\uae30\uac70\ub098, \uc9d1\uacc4\ub97c \uc218\ud589\ud560 \ub54c \uc720\uc6a9\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n

\uc608\uc81c 1: ROW_NUMBER() \uc0ac\uc6a9\ud558\uae30<\/h3>\n\n\n\n

\uc0ac\uc6a9\uc790 \ud14c\uc774\ube14\uc5d0\uc11c \ub098\uc774\uc5d0 \ub530\ub978 \uc0ac\uc6a9\uc790\uc758 \uc21c\uc704\ub97c \ub9e4\uae41\ub2c8\ub2e4. \ub098\uc774\uac00 \ub192\uc740 \uc0ac\uc6a9\uc790\ubd80\ud130 \ub0ae\uc740 \uc21c\uc11c\ub85c \uc21c\uc704\ub97c \ub9e4\uae41\ub2c8\ub2e4.<\/p>\n\n\n\n

<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span><\/span>\n  username,<\/span><\/span>\n  age,<\/span><\/span>\n  <\/span>ROW_NUMBER<\/span>() <\/span>OVER<\/span> (<\/span>ORDER BY<\/span> age <\/span>DESC<\/span>) <\/span>AS<\/span> row_number<\/span><\/span>\nFROM<\/span> users;<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
\uc774 \ucffc\ub9ac\ub294 users<\/code> \ud14c\uc774\ube14\uc758 \ubaa8\ub4e0 \uc0ac\uc6a9\uc790\uc5d0 \ub300\ud574 \ub098\uc774\ub97c \uae30\uc900\uc73c\ub85c \ub0b4\ub9bc\ucc28\uc21c \uc21c\uc704\ub97c \ub9e4\uae30\uace0, \uac01 \uc0ac\uc6a9\uc790\uc5d0 \ub300\ud574 \uace0\uc720\ud55c \uc21c\uc11c \ubc88\ud638(row_number<\/code>)\ub97c \ubd80\uc5ec\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n
\uc608\uc81c 2: RANK() \uc0ac\uc6a9\ud558\uae30<\/h3>\n\n\n\n
\ud310\ub9e4 \ub370\uc774\ud130\uac00 \uc788\ub294 \ud14c\uc774\ube14\uc5d0\uc11c \uac01 \ud310\ub9e4\uc6d0\uc758 \ud310\ub9e4\uc561\uc5d0 \ub530\ub978 \uc21c\uc704\ub97c \ub9e4\uae41\ub2c8\ub2e4. \uac19\uc740 \ud310\ub9e4\uc561\uc744 \uac00\uc9c4 \ud310\ub9e4\uc6d0\uc740 \uac19\uc740 \uc21c\uc704\ub97c \ubc1b\uc2b5\ub2c8\ub2e4.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span><\/span>\n  salesperson_id,<\/span><\/span>\n  sales_amount,<\/span><\/span>\n  <\/span>RANK<\/span>() <\/span>OVER<\/span> (<\/span>ORDER BY<\/span> sales_amount <\/span>DESC<\/span>) <\/span>AS<\/span> rank<\/span><\/span>\nFROM<\/span> sales;<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
\uc774 \ucffc\ub9ac\ub294 sales<\/code> \ud14c\uc774\ube14\uc5d0\uc11c sales_amount<\/code>\ub97c \uae30\uc900\uc73c\ub85c \uac01 \ud310\ub9e4\uc6d0\uc758 \ud310\ub9e4\uc561 \uc21c\uc704\ub97c \ub9e4\uae30\uba70, \uac19\uc740 \ud310\ub9e4\uc561\uc744 \uac00\uc9c4 \uacbd\uc6b0 \uac19\uc740 \uc21c\uc704\ub97c \ubd80\uc5ec\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n
\uc608\uc81c 3: SUM() \uc0ac\uc6a9\ud558\uae30<\/h3>\n\n\n\n
\uac01 \uc0ac\uc6a9\uc790\uc758 \ucd1d \uad6c\ub9e4\uc561\uc744 \uc5f0\ub3c4\ubcc4\ub85c \uc9d1\uacc4\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n
<\/circle><\/circle><\/circle><\/g><\/svg><\/span><\/path><\/path><\/svg><\/span>
SELECT<\/span><\/span>\n  user_id,<\/span><\/span>\n  <\/span>YEAR<\/span>(purchase_date) <\/span>AS<\/span> <\/span>year<\/span>,<\/span><\/span>\n  <\/span>SUM<\/span>(amount) <\/span>OVER<\/span> (<\/span>PARTITION<\/span> <\/span>BY<\/span> user_id, <\/span>YEAR<\/span>(purchase_date)) <\/span>AS<\/span> total_spent<\/span><\/span>\nFROM<\/span> purchases;<\/span><\/span>\n<\/span><\/code><\/pre><\/div>\n\n\n\n
\uc774 \ucffc\ub9ac\ub294 purchases<\/code> \ud14c\uc774\ube14\uc5d0\uc11c \uac01 \uc0ac\uc6a9\uc790(user_id<\/code>)\uc758 \uc5f0\ub3c4\ubcc4 \ucd1d \uad6c\ub9e4\uc561(total_spent<\/code>)\uc744 \uacc4\uc0b0\ud569\ub2c8\ub2e4. PARTITION BY<\/code> \uc808\uc744 \uc0ac\uc6a9\ud558\uc5ec \uac01 \uc0ac\uc6a9\uc790\uc640 \uc5f0\ub3c4\ubcc4\ub85c \ub370\uc774\ud130\ub97c \ubd84\ud560\ud558\uace0, \uadf8 \uc548\uc5d0\uc11c SUM(amount)<\/code>\ub97c \uacc4\uc0b0\ud558\uc5ec \ucd1d \uad6c\ub9e4\uc561\uc744 \uad6c\ud569\ub2c8\ub2e4.<\/p>\n\n\n\n
OVER<\/code> \uc808\uc740 \uc774\uc640 \uac19\uc774 \ub2e4\uc591\ud55c \uc708\ub3c4\uc6b0 \ud568\uc218\uc640 \ud568\uaed8 \uc0ac\uc6a9\ub418\uc5b4, \ub370\uc774\ud130\uc5d0 \ub300\ud55c \ubcf5\uc7a1\ud55c \uc9c8\uc758\uc640 \ubd84\uc11d\uc744 \uac00\ub2a5\ud558\uac8c \ud569\ub2c8\ub2e4.<\/p>\n\n\n\n

<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
SQL OVER \uc124\uba85<\/p>\n","protected":false},"author":1,"featured_media":1677,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"footnotes":""},"categories":[1],"tags":[139,117],"_links":{"self":[{"href":"https:\/\/dohokin.com\/index.php?rest_route=\/wp\/v2\/posts\/1676"}],"collection":[{"href":"https:\/\/dohokin.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/dohokin.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/dohokin.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/dohokin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1676"}],"version-history":[{"count":1,"href":"https:\/\/dohokin.com\/index.php?rest_route=\/wp\/v2\/posts\/1676\/revisions"}],"predecessor-version":[{"id":1679,"href":"https:\/\/dohokin.com\/index.php?rest_route=\/wp\/v2\/posts\/1676\/revisions\/1679"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/dohokin.com\/index.php?rest_route=\/wp\/v2\/media\/1677"}],"wp:attachment":[{"href":"https:\/\/dohokin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1676"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/dohokin.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1676"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/dohokin.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1676"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}