{"id":8590,"date":"2026-06-05T12:41:04","date_gmt":"2026-06-05T03:41:04","guid":{"rendered":"https:\/\/since2020.jp\/media\/?p=8590"},"modified":"2026-06-05T12:41:04","modified_gmt":"2026-06-05T03:41:04","slug":"sql-window-exec","status":"publish","type":"post","link":"https:\/\/since2020.jp\/media\/sql-window-exec\/","title":{"rendered":"SQLWindow\u95a2\u6570\u3068\u306f\uff1f\u301c\u5b9f\u884c\u9806\u3054\u3068\u306b\u89e3\u8aac\u301c"},"content":{"rendered":"\n

\u306f\u3058\u3081\u306b<\/h2>\n\n\n\n

Window\u95a2\u6570\u3068\u306f\u3001\u30a6\u30a3\u30f3\u30c9\u30a6\u3068\u547c\u3070\u308c\u308b\u7bc4\u56f2\u306e\u30ec\u30b3\u30fc\u30c9\u306b\u5bfe\u3057\u3066\u96c6\u8a08\u3084\u6f14\u7b97\u3092\u884c\u3046\u95a2\u6570\u3067\u3059\u3002<\/p>\n\n\n\n

\u4f7f\u7528\u30c7\u30fc\u30bf<\/h2>\n\n\n\n

emplyee\u30c6\u30fc\u30d6\u30eb<\/p>\n\n\n\n

id<\/td>name<\/td>department<\/td>salary<\/td>hire_date<\/td><\/tr>
1<\/td>\u7530\u4e2d \u592a\u90ce<\/td>\u55b6\u696d\u90e8<\/td>450000.0<\/td>2018-04-01<\/td><\/tr>
2<\/td>\u9234\u6728 \u82b1\u5b50<\/td>\u55b6\u696d\u90e8<\/td>520000.0<\/td>2016-04-01<\/td><\/tr>
3<\/td>\u4f50\u85e4 \u6b21\u90ce<\/td>\u55b6\u696d\u90e8<\/td>380000.0<\/td>2020-04-01<\/td><\/tr>
4<\/td>\u9ad8\u6a4b \u7f8e\u54b2<\/td>\u958b\u767a\u90e8<\/td>600000.0<\/td>2015-04-01<\/td><\/tr>
5<\/td>\u4f0a\u85e4 \u5065\u4e00<\/td>\u958b\u767a\u90e8<\/td>550000.0<\/td>2017-04-01<\/td><\/tr>
6<\/td>\u6e21\u8fba \u7531\u7f8e<\/td>\u958b\u767a\u90e8<\/td>480000.0<\/td>2019-04-01<\/td><\/tr>
7<\/td>\u5c71\u672c \u5927\u8f14<\/td>\u4eba\u4e8b\u90e8<\/td>500000.0<\/td>2014-04-01<\/td><\/tr>
8<\/td>\u4e2d\u6751 \u3055\u304f\u3089<\/td>\u4eba\u4e8b\u90e8<\/td>420000.0<\/td>2021-04-01<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

daily_sales\u30c6\u30fc\u30d6\u30eb<\/p>\n\n\n\n

date<\/td>sales<\/td><\/tr>
2025-05-01<\/td>120000.0<\/td><\/tr>
2025-05-02<\/td>135000.0<\/td><\/tr>
2025-05-03<\/td>98000.0<\/td><\/tr>
2025-05-04<\/td>142000.0<\/td><\/tr>
2025-05-05<\/td>158000.0<\/td><\/tr>
2025-05-06<\/td>110000.0<\/td><\/tr>
2025-05-07<\/td>125000.0<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

GROUP BY\u3068\u306e\u76f8\u9055\u70b9<\/h2>\n\n\n\n

Window\u95a2\u6570\u3068\u540c\u3058\u3088\u3046\u306b\u30ec\u30b3\u30fc\u30c9\u3092\u30b0\u30eb\u30fc\u30d7\u5316\u3059\u308bGROUP BY\u95a2\u6570\u3068\u3069\u3053\u304c\u7570\u306a\u308b\u306e\u3067\u3057\u3087\u3046\u304b\uff1f<\/p>\n\n\n\n

\u3053\u3053\u3067\u306femployees\u30c6\u30fc\u30d6\u30eb\u306bdepartment\u3054\u3068\u306b\u5e73\u5747\u8cc3\u91d1\u3092\u8ffd\u52a0\u3057\u305f\u3044\u6642\u3092\u8003\u3048\u3066\u307f\u307e\u3059\u3002<\/p>\n\n\n\n

GROUP BY\u95a2\u6570\u3092\u4f7f\u7528\u3059\u308b\u5834\u5408\u3060\u3068\u3001\u8907\u6570\u306e\u884c\u3092\u4e00\u3064\u306b\u307e\u3068\u3081\u3066\u5f8c\u3067employee\u30c6\u30fc\u30d6\u30eb\u3068\u7d50\u5408\u3057\u306a\u304f\u3066\u3044\u3051\u307e\u305b\u3093\u3002<\/p>\n\n\n\n

department<\/td>AVG(salary)<\/td><\/tr>
\u55b6\u696d\u90e8<\/td>450000.0<\/td><\/tr>
\u958b\u767a\u90e8<\/td>543333.333333<\/td><\/tr>
\u4eba\u4e8b\u90e8<\/td>460000.0<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

\u4e00\u65b9\u3001Window\u95a2\u6570\u306f\u5143\u306e\u30ec\u30b3\u30fc\u30c9\u3092\u6b8b\u3057\u305f\u307e\u307e\u3001\u5404\u30ec\u30b3\u30fc\u30c9\u306b\u5bfe\u3057\u3066\u96c6\u8a08\u7d50\u679c\u3092\u4ed8\u4e0e\u3057\u307e\u3059\u3002<\/p>\n\n\n\n

name<\/td>department<\/td>salary<\/td>dept_avg<\/td><\/tr>
\u5c71\u672c \u5927\u8f14<\/td>\u4eba\u4e8b\u90e8<\/td>500000.0<\/td>460000.0<\/td><\/tr>
\u4e2d\u6751 \u3055\u304f\u3089<\/td>\u4eba\u4e8b\u90e8<\/td>420000.0<\/td>460000.0<\/td><\/tr>
\u7530\u4e2d \u592a\u90ce<\/td>\u55b6\u696d\u90e8<\/td>450000.0<\/td>450000.0<\/td><\/tr>
\u9234\u6728 \u82b1\u5b50<\/td>\u55b6\u696d\u90e8<\/td>520000.0<\/td>450000.0<\/td><\/tr>
\u4f50\u85e4 \u6b21\u90ce<\/td>\u55b6\u696d\u90e8<\/td>380000.0<\/td>450000.0<\/td><\/tr>
\u9ad8\u6a4b \u7f8e\u54b2<\/td>\u958b\u767a\u90e8<\/td>600000.0<\/td>543333.333333<\/td><\/tr>
\u4f0a\u85e4 \u5065\u4e00<\/td>\u958b\u767a\u90e8<\/td>550000.0<\/td>543333.333333<\/td><\/tr>
\u6e21\u8fba \u7531\u7f8e<\/td>\u958b\u767a\u90e8<\/td>480000.0<\/td>543333.333333<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

\u3053\u306e\u6642\u4f7f\u7528\u3057\u3066\u3044\u308b\u30af\u30a8\u30ea<\/p>\n\n\n\n

SELECT name, department, salary,\n       AVG(salary) OVER (PARTITION BY department) AS dept_avg\nFROM employees;<\/code><\/pre>\n\n\n\n
\u306eAVG(salary) OVER (PARTITION BY department)<\/code>\u304cWindow\u95a2\u6570\u306e\u90e8\u5206\u3067PARTITION BY<\/code>\u304c\u30b0\u30eb\u30fc\u30d7\u5206\u3051\u3059\u308b\u57fa\u6e96(\u90e8\u9580\u3054\u3068\u3001\u9867\u5ba2\u3054\u3068\u306a\u3069)\u3092\u793a\u3057\u307e\u3059\u3002<\/p>\n\n\n\n
\u30e9\u30f3\u30ad\u30f3\u30b0\u7cfb\u306e\u95a2\u6570<\/h2>\n\n\n\n
\u30a6\u30a3\u30f3\u30c9\u30a6\u306e\u4e2d\u3067\u306e\u9806\u4f4d\u77e5\u308a\u305f\u3044\u5834\u5408\u306b\u4f7f\u7528\u3057\u307e\u3059\u3002<\/p>\n\n\n\n
\u4e3b\u306a\u95a2\u6570\u3068\u3057\u3066<\/p>\n\n\n\n
    \n
  • ROW_NUMBER()<\/code> \u2014 \u9023\u756a\uff081, 2, 3, 4…\uff09<\/li>\n\n\n\n
  • RANK()<\/code> \u2014 \u540c\u9806\u4f4d\u3042\u308a\u3001\u6b21\u306f\u98db\u3076\uff081, 2, 2, 4…\uff09<\/li>\n\n\n\n
  • DENSE_RANK()<\/code> \u2014 \u540c\u9806\u4f4d\u3042\u308a\u3001\u6b21\u306f\u98db\u3070\u306a\u3044\uff081, 2, 2, 3…<\/li>\n<\/ul>\n\n\n\n
    \u5b9f\u7528\u4f8b<\/h3>\n\n\n\n
    \u90e8\u9580\u5185\u306e\u7d66\u4e0e\u30e9\u30f3\u30ad\u30f3\u30b0<\/p>\n\n\n\n
    SELECT name, department, salary,\n       RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank\nFROM employees;<\/code><\/pre>\n\n\n\n
    department\u57fa\u6e96\u3067\u30b0\u30eb\u30fc\u30d7\u5206\u3051\u3057\u305f\u969b(PARTITION BY department)\u3001\u964d\u9806\u3067\u9806\u4f4d\u3065\u3051\u3057\u307e\u3059(RANK() \u2026. ORDER BY salary DESC)<\/p>\n\n\n\n
    name<\/td>department<\/td>salary<\/td>salary_rank<\/td><\/tr>
    \u5c71\u672c \u5927\u8f14<\/td>\u4eba\u4e8b\u90e8<\/td>500000.0<\/td>1<\/td><\/tr>
    \u4e2d\u6751 \u3055\u304f\u3089<\/td>\u4eba\u4e8b\u90e8<\/td>420000.0<\/td>2<\/td><\/tr>
    \u9234\u6728 \u82b1\u5b50<\/td>\u55b6\u696d\u90e8<\/td>520000.0<\/td>1<\/td><\/tr>
    \u7530\u4e2d \u592a\u90ce<\/td>\u55b6\u696d\u90e8<\/td>450000.0<\/td>2<\/td><\/tr>
    \u4f50\u85e4 \u6b21\u90ce<\/td>\u55b6\u696d\u90e8<\/td>380000.0<\/td>3<\/td><\/tr>
    \u9ad8\u6a4b \u7f8e\u54b2<\/td>\u958b\u767a\u90e8<\/td>600000.0<\/td>1<\/td><\/tr>
    \u4f0a\u85e4 \u5065\u4e00<\/td>\u958b\u767a\u90e8<\/td>550000.0<\/td>2<\/td><\/tr>
    \u6e21\u8fba \u7531\u7f8e<\/td>\u958b\u767a\u90e8<\/td>480000.0<\/td>3<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
    \u524d\u5f8c\u53c2\u7167\u7cfb\u306e\u95a2\u6570<\/h2>\n\n\n\n
    \u30a6\u30a3\u30f3\u30c9\u30a6\u306e\u4e2d\u3067\u306e\u524d\u5f8c\u306e\u6bd4\u8f03\u3092\u3057\u305f\u3044\u5834\u5408\u306e\u4f7f\u7528\u3057\u307e\u3059\u3002<\/p>\n\n\n\n
    \u4e3b\u306a\u95a2\u6570\u3068\u3057\u3066<\/p>\n\n\n\n
      \n
    • LAG(\u5217, n)<\/code> \u2014 n\u884c\u524d\u306e\u5024<\/li>\n\n\n\n
    • LEAD(\u5217, n)<\/code> \u2014 n\u884c\u5f8c\u306e\u5024<\/li>\n\n\n\n
    • FIRST_VALUE()<\/code>, LAST_VALUE()<\/code> \u2014 \u6700\u521d\/\u6700\u5f8c\u306e\u5024<\/li>\n<\/ul>\n\n\n\n
      \u5b9f\u7528\u4f8b<\/h3>\n\n\n\n
      \u524d\u65e5\u6bd4\u58f2\u4e0a<\/p>\n\n\n\n
      SELECT date, sales,\n       sales - LAG(sales, 1) OVER (ORDER BY date) AS diff_from_prev\nFROM daily_sales;<\/code><\/pre>\n\n\n\n
      \u65e5\u4ed8\u9806\u3067\u4e26\u3079\u305f\u3068\u304d(ORDER BY date)<\/code>\u30011\u3064\u524d\u306e\u30ec\u30b3\u30fc\u30c9\u306esales\u5024(LAG(sales, 1))<\/code>\u3002\u3064\u307e\u308asales - LAG(sales, 1) OVER (ORDER BY date) <\/code>\u306f\u6628\u65e5\u306b\u6bd4\u3079\u3066\u3069\u306e\u304f\u3089\u3044\u58f2\u308a\u4e0a\u3052\u304c\u4f38\u3073\u3066\u3044\u308b\u304b\u3092\u8868\u3059\u3002<\/p>\n\n\n\n
      date<\/td>sales<\/td>diff_from_prev<\/td><\/tr>
      2025-05-01<\/td>120000.0<\/td>null<\/td><\/tr>
      2025-05-02<\/td>135000.0<\/td>15000.0<\/td><\/tr>
      2025-05-03<\/td>98000.0<\/td>-37000.0<\/td><\/tr>
      2025-05-04<\/td>142000.0<\/td>44000.0<\/td><\/tr>
      2025-05-05<\/td>158000.0<\/td>16000.0<\/td><\/tr>
      2025-05-06<\/td>110000.0<\/td>-48000.0<\/td><\/tr>
      2025-05-07<\/td>125000.0<\/td>15000.0<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
      \u96c6\u8a08\u7cfb\u306e\u95a2\u6570<\/h2>\n\n\n\n
      \u30c7\u30fc\u30bf\u306e\u7279\u5fb4\u3092\u898b\u305f\u3044\u3068\u304d\u306b\u4f7f\u7528\u3059\u308b<\/p>\n\n\n\n
      \u4e3b\u306a\u95a2\u6570\u3068\u3057\u3066SUM()<\/code>, AVG()<\/code>, COUNT()<\/code>, MAX()<\/code>, MIN()<\/code><\/p>\n\n\n\n
      \u5b9f\u7528\u4f8b<\/h3>\n\n\n\n
      \u79fb\u52d5\u5e73\u5747(\u76f4\u8fd13\u65e5)<\/p>\n\n\n\n
      SELECT date, sales,\n       AVG(sales) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg\nFROM daily_sales;<\/code><\/pre>\n\n\n\n
      \u65e5\u4ed8\u9806\u306b\u4e26\u3079\u3066(ORDER BY date)\u30012\u884c\u524d\u304b\u3089(ROWS BETWEEN 2 PRECEDING)\u3001\u73fe\u5728\u306e\u884c\u307e\u3067(CURRENT ROW)\u3092\u30a6\u30a3\u30f3\u30c9\u30a6\u306e\u5bfe\u8c61\u3068\u3057\u3066\u3001\u305d\u306e\u5e73\u5747\u3092\u8a08\u7b97\u3057\u307e\u3059\u3002<\/p>\n\n\n\n
      date<\/td>sales<\/td>moving_avg<\/td><\/tr>
      2025-05-01<\/td>120000.0<\/td>120000.0<\/td><\/tr>
      2025-05-02<\/td>135000.0<\/td>127500.0<\/td><\/tr>
      2025-05-03<\/td>98000.0<\/td>117666.666667<\/td><\/tr>
      2025-05-04<\/td>142000.0<\/td>125000.0<\/td><\/tr>
      2025-05-05<\/td>158000.0<\/td>132666.666667<\/td><\/tr>
      2025-05-06<\/td>110000.0<\/td>136666.666667<\/td><\/tr>
      2025-05-07<\/td>125000.0<\/td>131000.0<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
      \u307e\u3068\u3081<\/h2>\n\n\n\n
      \u3053\u306e\u3088\u3046\u306bWindow\u95a2\u6570\u3092\u4f7f\u7528\u3067\u304d\u308b\u3088\u3046\u306b\u306a\u308c\u3070\u9577\u304f\u8907\u96d1\u306a\u30af\u30a8\u30ea\u3092\u304b\u304f\u5fc5\u8981\u304c\u306a\u304f\u306a\u3063\u305f\u308a\u3001\u3088\u308a\u76f4\u611f\u7684\u306b\u30af\u30a8\u30ea\u306e\u304b\u3051\u308b\u3088\u3046\u306b\u306a\u308b\u306e\u3067\u662f\u975e\u4f7f\u3044\u3053\u306a\u3057\u3066\u307f\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
      \u306f\u3058\u3081\u306b Window\u95a2\u6570\u3068\u306f\u3001\u30a6\u30a3\u30f3\u30c9\u30a6\u3068\u547c\u3070\u308c\u308b\u7bc4\u56f2\u306e\u30ec\u30b3\u30fc\u30c9\u306b\u5bfe\u3057\u3066\u96c6\u8a08\u3084\u6f14\u7b97\u3092\u884c\u3046\u95a2\u6570\u3067\u3059\u3002 \u4f7f\u7528\u30c7\u30fc\u30bf emplyee\u30c6\u30fc\u30d6\u30eb id name department salary hire_date 1 \u7530\u4e2d  […]<\/p>\n","protected":false},"author":14,"featured_media":8422,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"content-type":"","swell_btn_cv_data":"","footnotes":"","_wp_rev_ctl_limit":""},"categories":[1251],"tags":[148,39,26],"class_list":["post-8590","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-analysis","tag-sql","tag-39","tag-26"],"_links":{"self":[{"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/posts\/8590","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/users\/14"}],"replies":[{"embeddable":true,"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/comments?post=8590"}],"version-history":[{"count":1,"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/posts\/8590\/revisions"}],"predecessor-version":[{"id":8605,"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/posts\/8590\/revisions\/8605"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/media\/8422"}],"wp:attachment":[{"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/media?parent=8590"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/categories?post=8590"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/since2020.jp\/media\/wp-json\/wp\/v2\/tags?post=8590"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}