{"id":500,"date":"2026-05-17T07:03:29","date_gmt":"2026-05-16T23:03:29","guid":{"rendered":"https:\/\/junai.ai\/blog\/pg-window-functions-11\/"},"modified":"2026-05-17T07:03:29","modified_gmt":"2026-05-16T23:03:29","slug":"pg-window-functions-11","status":"publish","type":"post","link":"https:\/\/junai.ai\/blog\/pg-window-functions-11\/","title":{"rendered":"\uc708\ub3c4\uc6b0 \ud568\uc218 \u2014 OVER\u00b7PARTITION\u00b7RANK (\uae30\ucd08 11\ud3b8)"},"content":{"rendered":"\n<!-- WordPress REST API \ubc1c\ud589\uc6a9 HTML (\uc790\ub3d9 \uc0dd\uc131) -->\n<!-- WP-FEATURED-MEDIA-ID: 287 -->\n<div style=\"max-width:800px;margin:0 auto;\">\n<style>\n:root{--color-primary:#336791;--color-accent:#60a5fa;--color-bg:#fafafa;--color-bg-card:#ffffff;--color-text:#1e293b;--color-text-muted:#64748b;--hero-start:#0f172a;--hero-end:#336791;--font-body:-apple-system,BlinkMacSystemFont,'Apple SD Gothic Neo','Noto Sans KR',sans-serif;--size-body:17px;--line-height:1.75;}\n*{box-sizing:border-box;}\n.container{max-width:760px;margin:0 auto;padding:0 22px 80px;}\n.hero{background:linear-gradient(135deg,var(--hero-start) 0%,var(--hero-end) 100%);color:#fff;padding:72px 22px 56px;text-align:center;}\n.hero .badge{display:inline-block;background:rgba(96,165,250,0.18);color:var(--color-accent);padding:6px 14px;border-radius:999px;font-size:13px;font-weight:600;letter-spacing:0.5px;margin-bottom:18px;}\n.hero h1{margin:0 0 18px;font-size:36px;line-height:1.3;letter-spacing:-0.3px;}\n.hero p.sub{margin:0 auto;max-width:580px;font-size:17px;color:#bfdbfe;}\n.hero img{width:100%;max-width:720px;height:auto;margin:36px auto 0;display:block;border-radius:10px;box-shadow:0 8px 32px rgba(0,0,0,0.3);}\n.meta{display:flex;gap:14px;justify-content:center;margin-top:20px;font-size:13px;color:#93c5fd;flex-wrap:wrap;}\n.meta span::before{content:\"\u00b7\";margin-right:14px;color:#1e3a8a;}\n.meta span:first-child::before{content:\"\";margin:0;}\narticle{background:var(--color-bg-card);margin-top:-36px;padding:44px 28px;border-radius:14px;box-shadow:0 2px 18px rgba(0,0,0,0.06);}\narticle p{margin:0 0 18px;}\nh2{font-size:28px;line-height:1.35;letter-spacing:-0.3px;margin:48px 0 18px;padding-bottom:10px;border-bottom:2px solid var(--color-primary);}\nh2:first-of-type{margin-top:8px;}\nh3{font-size:21px;line-height:1.4;margin:30px 0 12px;color:var(--color-primary);}\ncode{background:#f1f5f9;color:#0f172a;padding:2px 6px;border-radius:4px;font-family:'SFMono-Regular',Menlo,Consolas,monospace;font-size:0.92em;}\npre{background:#0f172a;color:#e2e8f0;padding:18px 20px;border-radius:10px;overflow-x:auto;font-size:14.5px;line-height:1.65;margin:18px 0;}\npre code{background:transparent;color:inherit;padding:0;}\nul,ol{margin:0 0 18px;padding-left:24px;}\nli{margin-bottom:8px;}\nstrong{color:#0f172a;}\n.databox{background:#eff6ff;border-left:4px solid var(--color-primary);padding:18px 20px;border-radius:6px;margin:22px 0;}\n.databox p{margin:0 0 8px;}\n.databox p:last-child{margin:0;}\n.databox strong{color:var(--color-primary);}\n.warnbox{background:linear-gradient(135deg,#fef3c7 0%,#fde68a 100%);border-left:4px solid #d97706;padding:18px 20px;border-radius:6px;margin:22px 0;}\n.warnbox strong{color:#92400e;}\n.tablewrap{overflow-x:auto;-webkit-overflow-scrolling:touch;margin:22px 0;}\ntable{width:100%;border-collapse:collapse;font-size:15px;background:var(--color-bg-card);}\nth,td{padding:11px 12px;text-align:left;border-bottom:1px solid #e2e8f0;vertical-align:top;}\nth{background:#f1f5f9;font-weight:700;color:#0f172a;}\ntd:first-child,th:first-child{font-weight:700;}\n.cta{background:linear-gradient(135deg,#336791 0%,#60a5fa 100%);color:#fff;padding:30px 24px;border-radius:12px;margin-top:44px;text-align:center;}\n.cta h3{color:#fff;margin:0 0 10px;}\n.cta p{margin:0;color:#dbeafe;}\n.series-nav{background:#eff6ff;padding:18px 22px;border-radius:10px;margin-top:24px;font-size:14.5px;color:var(--color-text-muted);}\n.series-nav strong{color:var(--color-primary);}\n@media (max-width:480px){.hero{padding:52px 18px 44px;}.hero h1{font-size:26px;}.hero p.sub{font-size:15px;}article{padding:28px 18px;border-radius:10px;}h2{font-size:22px;}h3{font-size:18px;}body{font-size:16px;}pre{font-size:13px;padding:14px 16px;}}\n@media (max-width:560px){.tablewrap table,.tablewrap thead,.tablewrap tbody,.tablewrap tr,.tablewrap th,.tablewrap td{display:block;width:auto;}.tablewrap thead{display:none;}.tablewrap tr{margin:0 0 14px;border:1px solid #e2e8f0;border-radius:10px;overflow:hidden;}.tablewrap td{border:none;border-bottom:1px solid #f1f5f9;padding:9px 14px;}.tablewrap td:first-child{background:#eff6ff;font-weight:800;font-size:15.5px;}.tablewrap td:last-child{border-bottom:none;}.tablewrap td[data-label]::before{content:attr(data-label) \" \u2014 \";font-weight:700;color:var(--color-primary);}}\n<\/style>\n<header class=\"hero\">\n  <span class=\"badge\">PostgreSQL \uad50\uc7ac \u00b7 11\ud3b8 \/ 24\ud3b8<\/span>\n  <h1>\uc708\ub3c4\uc6b0 \ud568\uc218 \u2014 OVER\u00b7PARTITION\u00b7ROW_NUMBER<\/h1>\n  <p class=\"sub\">&#8220;GROUP BY \uc548 \ud558\uace0 \uc606 \ud589\uc774\ub791 \ube44\uad50&#8221; \u2014 \ubd84\uc11d SQL \uc758 \ud575\uc2ec \ub3c4\uad6c.<\/p>\n  <div class=\"meta\"><span>SQL \uae30\ucd08<\/span><span>\uc77d\ub294 \uc2dc\uac04 8\ubd84<\/span><span>2026-05-17<\/span><\/div>\n  <img decoding=\"async\" src=\"https:\/\/junai.ai\/blog\/wp-content\/uploads\/2026\/05\/hero-66.jpg\" alt=\"\uc708\ub3c4\uc6b0 \ud568\uc218\uac00 \ud55c \ud589\uc5d0 \uadf8\ub8f9\ubcc4 \uc21c\uc704\/\uc774\uc804 \uac12\uc744 \ubd99\uc774\ub294 \ub3c4\uc2dd\">\n<\/header>\n\n<div class=\"container\">\n<article>\n\n<p>GROUP BY \ub294 \ud589\uc744 \ud569\uccd0 \ud1b5\uacc4\ub97c \ub0c5\ub2c8\ub2e4. \ud558\uc9c0\ub9cc &#8220;\uac01 \ud589\uc758 \ub370\uc774\ud130\ub294 \uadf8\ub300\ub85c \ub450\uace0 \uc606 \uadf8\ub8f9 \ud1b5\uacc4\ub098 \uc21c\uc704\ub97c \ud55c \uceec\ub7fc\uc73c\ub85c \ubd99\uc774\uace0 \uc2f6\ub2e4&#8221; \uba74? \uadf8\uac8c <strong>\uc708\ub3c4\uc6b0 \ud568\uc218<\/strong>\uc785\ub2c8\ub2e4. SQL \uc758 \uac00\uc7a5 \uac15\ub825\ud55c \ub3c4\uad6c \uc911 \ud558\ub098 \u2014 11\ud3b8\uc740 \ub9e4\uc77c \ub9cc\ub098\ub294 6\uac1c\ub97c \uc815\ub9ac\ud569\ub2c8\ub2e4.<\/p>\n\n<h2>OVER() \u2014 \uc708\ub3c4\uc6b0 \ud568\uc218\uc758 \ud575\uc2ec<\/h2>\n\n<pre><code>-- \uc77c\ubc18 \uc9d1\uacc4 \u2014 \ud589\uc774 \uc904\uc5b4\ub4e6\nSELECT category_id, COUNT(*) FROM products GROUP BY category_id;\n\n-- \uc708\ub3c4\uc6b0 \u2014 \ubaa8\ub4e0 \ud589\uc774 \uc0b4\uc544\ub0a8\uc74c + \uadf8\ub8f9 \ud1b5\uacc4\uac00 \ud55c \uceec\ub7fc\uc73c\ub85c\nSELECT id, name, category_id,\n       COUNT(*) OVER (PARTITION BY category_id) AS category_count\nFROM   products;\n-- \uacb0\uacfc: \ubaa8\ub4e0 \uc0c1\ud488 + \uac01\uc790 \uc18d\ud55c \uce74\ud14c\uace0\ub9ac\uc758 \ucd1d \uac1c\uc218<\/code><\/pre>\n\n<div class=\"databox\">\n  <p><strong>\ud575\uc2ec \ud55c \uc904.<\/strong> \uc77c\ubc18 \uc9d1\uacc4 = &#8220;\ud589\uc744 \ud569\uce5c\ub2e4&#8221;. \uc708\ub3c4\uc6b0 = &#8220;\ud589\uc744 \ud569\uce58\uc9c0 \uc54a\uace0, \uadf8\ub8f9 \ud1b5\uacc4\ub97c \uc606\uc5d0 \ubd99\uc778\ub2e4&#8221;. <code>OVER ()<\/code> \uac00 \uc708\ub3c4\uc6b0\uc758 \ud2b8\ub9ac\uac70.<\/p>\n<\/div>\n\n<h2>ROW_NUMBER \u00b7 RANK \u00b7 DENSE_RANK \u2014 \uc21c\uc704 3\uc885<\/h2>\n\n<pre><code>-- \uce74\ud14c\uace0\ub9ac\ubcc4 \uac00\uaca9 \uc21c\uc704\nSELECT name, category_id, price,\n       ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn,\n       RANK()       OVER (PARTITION BY category_id ORDER BY price DESC) AS rk,\n       DENSE_RANK() OVER (PARTITION BY category_id ORDER BY price DESC) AS drk\nFROM   products;\n\n-- \ub3d9\uc810 \ucc98\ub9ac \ucc28\uc774 (\uac00\uaca9 10000, 10000, 8000, 5000)\n-- ROW_NUMBER : 1, 2, 3, 4   \u2190 \ub3d9\uc810\ub3c4 \ub2e4\ub978 \ubc88\ud638\n-- RANK       : 1, 1, 3, 4   \u2190 \ub3d9\uc810\uc740 \uac19\uc740 \uc21c\uc704, \ub2e4\uc74c\uc740 \uac74\ub108\ub700\n-- DENSE_RANK : 1, 1, 2, 3   \u2190 \ub3d9\uc810\uc740 \uac19\uc740 \uc21c\uc704, \ub2e4\uc74c\uc740 \uc548 \uac74\ub108\ub700<\/code><\/pre>\n\n<h2>\uc2e4\uc804 \u2014 \uce74\ud14c\uace0\ub9ac\ubcc4 1\uc704\ub9cc \ubf51\uae30<\/h2>\n\n<pre><code>-- \"\uac01 \uce74\ud14c\uace0\ub9ac\uc5d0\uc11c \uac00\uaca9 1\uc704 \uc0c1\ud488\"\nWITH ranked AS (\n  SELECT *,\n         ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn\n  FROM   products\n)\nSELECT * FROM ranked WHERE rn = 1;<\/code><\/pre>\n\n<p>\uac00\uc7a5 \uc790\uc8fc \uc4f0\ub294 \ud328\ud134\uc785\ub2c8\ub2e4. JOIN + GROUP BY \ub85c \ub9cc\ub4e4\uba74 \ubcf5\uc7a1\ud574\uc9c0\ub294\ub370, ROW_NUMBER + PARTITION \uc73c\ub85c \uae54\ub054\ud558\uac8c.<\/p>\n\n<h2>LAG \u00b7 LEAD \u2014 \uc774\uc804\/\ub2e4\uc74c \ud589\uacfc \ube44\uad50<\/h2>\n\n<pre><code>-- \uc77c\ubcc4 \ub9e4\ucd9c\uc758 \uc804\uc77c \ub300\ube44 \uc99d\uac10\nSELECT day,\n       revenue,\n       LAG(revenue, 1)  OVER (ORDER BY day) AS prev,\n       revenue - LAG(revenue, 1) OVER (ORDER BY day) AS diff,\n       LEAD(revenue, 1) OVER (ORDER BY day) AS next\nFROM   daily_revenue\nORDER BY day;\n\n-- \uc0ac\uc6a9\uc790\ubcc4 \uc774\uc804 \uc8fc\ubb38 \uc2dc\uac04\ucc28\nSELECT user_id, created_at,\n       LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_at,\n       created_at - LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS gap\nFROM   orders;<\/code><\/pre>\n\n<div class=\"databox\">\n  <p><strong>LAG\/LEAD \uc758 \uc77c\uc0c1.<\/strong> &#8220;\uc804\uc77c \ub300\ube44&#8221;, &#8220;\uc774\uc804 \ud589\uacfc\uc758 \uc2dc\uac04\ucc28&#8221;, &#8220;\ub2e4\uc74c \ub2e8\uacc4\uae4c\uc9c0 \ub0a8\uc740 \uc2dc\uac04&#8221; \u2014 \ubd84\uc11d \ucffc\ub9ac\uc758 \ud575\uc2ec \ud328\ud134. \uc61b\ub0a0\uc5d4 self-join \uc73c\ub85c \ud588\uc9c0\ub9cc LAG \uac00 \ud6e8\uc52c \uae54\ub054\ud569\ub2c8\ub2e4.<\/p>\n<\/div>\n\n<h2>SUM\/AVG OVER \u2014 \ub204\uc801\u00b7\uc774\ub3d9 \ud3c9\uade0<\/h2>\n\n<pre><code>-- \ub204\uc801 \ub9e4\ucd9c\nSELECT day, revenue,\n       SUM(revenue) OVER (ORDER BY day) AS running_total\nFROM   daily_revenue;\n\n-- 7\uc77c \uc774\ub3d9 \ud3c9\uade0\nSELECT day, revenue,\n       AVG(revenue) OVER (\n         ORDER BY day\n         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW\n       ) AS moving_7d_avg\nFROM   daily_revenue;<\/code><\/pre>\n\n<h2>FIRST_VALUE \u00b7 LAST_VALUE \u00b7 NTH_VALUE<\/h2>\n\n<pre><code>-- \uac01 \uce74\ud14c\uace0\ub9ac\uc758 \ucd5c\uace0 \uac00\uaca9\uc744 \ubaa8\ub4e0 \ud589\uc5d0 \ubd99\uc774\uae30\nSELECT name, price, category_id,\n       FIRST_VALUE(price) OVER (\n         PARTITION BY category_id ORDER BY price DESC\n       ) AS category_top_price,\n       price * 100.0 \/ FIRST_VALUE(price) OVER (\n         PARTITION BY category_id ORDER BY price DESC\n       ) AS pct_of_top\nFROM   products;<\/code><\/pre>\n\n<h2>frame (RANGE\/ROWS) \u2014 \uc708\ub3c4\uc6b0\uc758 \ubc94\uc704 \ubbf8\uc138 \uc870\uc815<\/h2>\n\n<div class=\"tablewrap\">\n<table>\n  <thead>\n    <tr><th>\uad6c\ubb38<\/th><th>\uc758\ubbf8<\/th><\/tr>\n  <\/thead>\n  <tbody>\n    <tr><td data-label=\"\uad6c\ubb38\">ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW<\/td><td data-label=\"\uc758\ubbf8\">\ucc98\uc74c\ubd80\ud130 \ud604\uc7ac\uae4c\uc9c0 (\uae30\ubcf8\uac12 with ORDER BY)<\/td><\/tr>\n    <tr><td data-label=\"\uad6c\ubb38\">ROWS BETWEEN 6 PRECEDING AND CURRENT ROW<\/td><td data-label=\"\uc758\ubbf8\">\ucd5c\uadfc 7\ud589 (\uc774\ub3d9 \ud3c9\uade0)<\/td><\/tr>\n    <tr><td data-label=\"\uad6c\ubb38\">ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING<\/td><td data-label=\"\uc758\ubbf8\">\uc804\uccb4 \uc708\ub3c4\uc6b0 (= ORDER BY \uc5c6\ub294 \ud6a8\uacfc)<\/td><\/tr>\n    <tr><td data-label=\"\uad6c\ubb38\">RANGE BETWEEN &#8216;1 day&#8217; PRECEDING AND CURRENT ROW<\/td><td data-label=\"\uc758\ubbf8\">\uac12 \uae30\uc900(\uc2dc\uac04 \ub4f1)<\/td><\/tr>\n  <\/tbody>\n<\/table>\n<\/div>\n\n<div class=\"warnbox\">\n  <p><strong>\ud754\ud55c \ud568\uc815 \u2014 LAST_VALUE.<\/strong> <code>LAST_VALUE() OVER (ORDER BY x)<\/code> \uae30\ubcf8 \ud504\ub808\uc784\uc740 &#8220;\ucc98\uc74c~\ud604\uc7ac&#8221; \ub77c\uc11c LAST_VALUE \uac00 \ub9e4\ubc88 \ud604\uc7ac \ud589 \uc790\uc2e0\uc744 \ubc18\ud658\ud569\ub2c8\ub2e4. \uc2e4\uc81c &#8220;\uc804\uccb4 \ub9c8\uc9c0\ub9c9&#8221; \uc744 \uc6d0\ud558\uba74 <code>ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING<\/code> \uba85\uc2dc.<\/p>\n<\/div>\n\n<h2>WINDOW \uc808 \u2014 \uc7ac\uc0ac\uc6a9<\/h2>\n\n<pre><code>-- \uac19\uc740 \uc708\ub3c4\uc6b0\ub97c \uc5ec\ub7ec \ud568\uc218\uc5d0 \uc4f8 \ub54c\nSELECT id, name, price,\n       ROW_NUMBER() OVER w AS rn,\n       SUM(price)   OVER w AS running\nFROM   products\nWINDOW w AS (PARTITION BY category_id ORDER BY price DESC);<\/code><\/pre>\n\n<h2>\uc708\ub3c4\uc6b0 \ud568\uc218 vs GROUP BY \u2014 \ud55c \ud45c<\/h2>\n\n<div class=\"tablewrap\">\n<table>\n  <thead>\n    <tr><th><\/th><th>GROUP BY<\/th><th>\uc708\ub3c4\uc6b0 \ud568\uc218<\/th><\/tr>\n  <\/thead>\n  <tbody>\n    <tr><td data-label=\"\">\ud589 \uc218<\/td><td data-label=\"G\">\uc904\uc5b4\ub4e6<\/td><td data-label=\"W\">\uadf8\ub300\ub85c<\/td><\/tr>\n    <tr><td data-label=\"\">\ud1b5\uacc4 \uc704\uce58<\/td><td data-label=\"G\">\uacb0\uacfc \ud589<\/td><td data-label=\"W\">\uac01 \uc6d0\ubcf8 \ud589 \uc606\uc5d0<\/td><\/tr>\n    <tr><td data-label=\"\">\uc21c\uc704\u00b7\uc774\uc804\/\ub2e4\uc74c<\/td><td data-label=\"G\">\ubd88\uac00<\/td><td data-label=\"W\">RANK\/LAG\/LEAD<\/td><\/tr>\n    <tr><td data-label=\"\">\ub300\ud45c \uc6a9\ub3c4<\/td><td data-label=\"G\">\ud1b5\uacc4 \uc9d1\uacc4<\/td><td data-label=\"W\">\uc21c\uc704\u00b7\ub204\uc801\u00b7\uc804\uc77c \ub300\ube44<\/td><\/tr>\n  <\/tbody>\n<\/table>\n<\/div>\n\n<div class=\"cta\">\n  <h3>12\ud3b8 \u2014 JSON\u00b7jsonb \ub2e4\ub8e8\uae30<\/h3>\n  <p>->\u00b7->>\u00b7@> \uc5f0\uc0b0\uc790, jsonb_set, GIN \uc778\ub371\uc2a4, \uc5b8\uc81c jsonb \uac00 \uc801\uc808\ud55c\uac00.<\/p>\n<\/div>\n\n<div class=\"series-nav\">\n  <strong>\ud83d\udcda PostgreSQL \ubc30\uc6b0\uae30 \uad50\uc7ac<\/strong><br>\n  \uc774\uc804: 10\ud3b8 \uc11c\ube0c\ucffc\ub9ac\u00b7CTE \u00b7 \ud604\uc7ac: <strong>11\ud3b8 (SQL \uae30\ucd08)<\/strong> \u00b7 \ub2e4\uc74c \u2192 12\ud3b8 JSON\u00b7jsonb \u00b7 \uc9c4\ud589: <strong>11\/24<\/strong>\n<\/div>\n\n<\/article>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>ROW_NUMBER\u00b7RANK\u00b7LAG\u00b7LEAD\u00b7PARTITION. 24\ud3b8 \uad50\uc7ac 11\ud3b8.<\/p>\n","protected":false},"author":1,"featured_media":287,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19],"tags":[],"class_list":["post-500","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/posts\/500","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/comments?post=500"}],"version-history":[{"count":0,"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/posts\/500\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/media\/287"}],"wp:attachment":[{"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/media?parent=500"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/categories?post=500"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/junai.ai\/blog\/wp-json\/wp\/v2\/tags?post=500"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}