場所分雷-復習5

2688 ワード
テキストリンク 
SELECT product_id
     , score
     , ROW_NUMBER() OVER(ORDER BY score DESC) AS row
     , SUM(score) OVER(ORDER BY score DESC
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
                        AS cum_score
     , AVG(score) OVER(ORDER BY score DESC
                        ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
                        AS local_avg
     , FIRST_VALUE(product_id) OVER (ORDER BY score DESC
                                      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
                                      AS first_value
     , LAST_VALUE(product_id) OVER(ORDER BY score DESC
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
                                    AS last_value
  FROM popular_products
  ORDER BY row

ORDER BY構文とSUM/ABGなどの密関数を組み合わせることで、密関数の適用範囲を柔軟に指定できます.
FIRST VALUE/LAST VALUE:ウィンドウの最初のレコードと最後のレコードの関数をそれぞれ抽出します.
フレーム指定文には複数のタイプがあります.
基本はROWS BETWEEN start and endです.
startとendには、次のキーワードが表示される場合があります.
CURRENT ROW:現在の行
n PRECEDING:n行の前
n FOLLOWING:n行後
UNBOUNDED PRECEDING:前の行のすべて
UNBOUNDED FOLLOWING:すべての後続行
SELECT category
     , product_id
     , score
     , ROW_NUMBER() OVER(PARTITION BY category 
                         ORDER BY score DESC) AS row
     , RANK() OVER(PARTITION BY category
                   ORDER BY score DESC) AS rank
     , DENSE_RANK() OVER(PARTITION BY category
                         ORDER BY score DESC) AS dense_rank
  FROM popular_products
  ORDER BY category, row
SELECT *
  FROM (SELECT category
             , product_id
             , score
             , ROW_NUMBER() OVER(PARTITION BY category
                                 ORDER BY score DESC) AS rank
          FROM popular_products) AS popular_products_with_rank
  WHERE rank <= 2
  ORDER BY category, rank

カテゴリ上位2位の商品を抽出したアンケートです.
SELECT DISTINCT category
     , FIRST_VALUE(product_id) OVER(PARTITION BY category
                                    ORDER BY score DESC
                                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
                                    AS product_id
  FROM popular_products
上位1商品IDをカテゴリ順に抽出する場合は、FIRST VALUEウィンドウ関数を使用し、SELECT DISTINT構文を使用して結果をまとめることができます.
[jQuery] accordion
Windowsプログラミング:開始