All rights reserved. 12 デジタル庁におけるガバメントクラウド整備のためのクラウド サービスの提供-令和4年度募集-の公募結果について https://www.digital.go.jp/assets/contents/node/basic_page/field_ref_resources/d6b5753c-c4eb-4ee6- 92d0-21b3fa945a82/b90d9207/20221003_policies_gov_cloud_outline_01.pdf https://www.oracle.com/jp/news/announcement/oracle-japan-selected-as-government-cloud-service provider-2022-10-06/
sales_date price tokyo 2023-01-01 1200 osaka 2023-01-01 1000 nagoya 2023-01-01 1900 tokyo 2023-01-02 1000 osaka 2023-01-02 1200 nagoya 2023-01-02 1200 tokyo 2023-01-03 1100 osaka 2023-01-03 1700 nagoya 2023-01-03 1600 tokyo 2023-01-04 1300 osaka 2023-01-04 1000 nagoya 2023-01-04 1200 tokyo 2023-01-05 1500 osaka 2023-01-05 1100 nagoya 2023-01-05 1000 area sales_date price row_number tokyo 2023-01-05 1500 1 tokyo 2023-01-04 1300 2 tokyo 2023-01-01 1200 3 tokyo 2023-01-03 1100 4 tokyo 2023-01-02 1000 5 osaka 2023-01-03 1700 1 osaka 2023-01-02 1200 2 osaka 2023-01-05 1100 3 osaka 2023-01-01 1000 4 osaka 2023-01-04 1000 5 nagoya 2023-01-01 1900 1 nagoya 2023-01-03 1600 2 nagoya 2023-01-02 1200 3 nagoya 2023-01-04 1200 4 nagoya 2023-01-05 1000 5 SELECT * ,row_number() OVER (PARTITION BY area ORDER BY price DESC) rn FROM sales SELECT * FROM ( SELECT * ,row_number() OVER (PARTITION BY area ORDER BY price DESC) rn FROM sales) sales_rn WHERE rn <= 3 ; area sales_date price row_number tokyo 2023-01-05 1500 1 tokyo 2023-01-04 1300 2 tokyo 2023-01-01 1200 3 tokyo 2023-01-03 1100 4 tokyo 2023-01-02 1000 5 osaka 2023-01-03 1700 1 osaka 2023-01-02 1200 2 osaka 2023-01-05 1100 3 osaka 2023-01-01 1000 4 osaka 2023-01-04 1000 5 nagoya 2023-01-01 1900 1 nagoya 2023-01-03 1600 2 nagoya 2023-01-02 1200 3 nagoya 2023-01-04 1200 4 nagoya 2023-01-05 1000 5 v14までは不要行を含めたすべて計算したうえで最後にWHERE条件を適用 PostgreSQL15 ではウィンドウ関数処理時点で不要な結果を除去