新しく知ったこと２つ

明日詳しく書きます。

countするときのover

以下のようなsalesテーブルがあるとき

id int
item_id int
price int

で、こんな項目が結果として欲しいときね(sum_price以降は、item_idごとの集計)

id,sales.price,item_id,sum_price,min_price,max_price,avg_price,cnt_price

今までおいらはこんなSQLを書いてました。

SELECT
    id,
    sales.price,
    sales.item_id,
    sum_price,
    min_price,
    max_price,
    avg_price,
    cnt_price
FROM
    sales left join (SELECT
                        item_id,
                        sum(price) as asum_price,
                        min(price) as min_price,
                        max(price) as max_price,
                        avg(price) as avg_price ,
                        count(price) as cnt_price
                    FROM
                        sales
                    GROUP BY
                        item_id
                    ) as a on sales.item_id = a.item_id
ORDER BY
    item_id

こんな感じでも書けます

SELECT
    id,
    price,
    item_id,
    sum(price) over(partition by item_id),
    min(price) over(partition by item_id),
    max(price) over(partition by item_id),
    avg(price) over(partition by item_id),
    count(price) over(partition by item_id)
FROM
    sales

withを使った再帰的な文字列の結合

たとえば20商品入ったitemsテーブルの中で、全商品の商品名(name)項目をカンマ区切りで表示したい場合

WITH x(list,len,itemid,cnt) as
(SELECT
    cast(name as varchar(max)),
    1,
    id,
    20
FROM
    items
UNION ALL
SELECT
    cast(x.list+','+i.name as varchar(max)),
    x.len+1,
    i.id,
    x.cnt
FROM
    items i,
    x
WHERE
    i.id > x.itemid
)
SELECT list FROM x WHERE len = cnt

内部の詳しい動きはあまり理解できてません。。。
with xで定義したSELECTの中でさらにxを呼び出し、再帰的にname項目を連結しています。
もうちょっと理解が深まったら解説書きますね