SQL 同じユーザーが同じ品を購入した場合、商品の個数を合計し一行に纏めたい

Question

現行のスクリプトを追記させて頂きました。

同じユーザーが同じ品を購入した件数が複数行に分かれている場合、それらの行の購入件数（cntとratio列）を合計し一行として表示したいのですが、SQLのクエリーでその様な処理が可能でしょうか？以下の例ですとappleを購入したcustomer-aが対象となり、処理後のデータの通り購入件数が合計され7となるイメージです。sum関数にcase式を組み合わせる等、色々調べ試してみましたが有効な方法が見つからずおります。どなたかヒントやアドバイス頂けますと大変ありがたいです。

現行スクリプト実行結果
products cnt ratio purchasedBy
apple 2 12% customer-a
orange 3 18% customer-b
apple 5 31% customer-a
orange 4 25% customer-c
apple 2 12% customer-d

期待する処理後の結果
products cnt ratio purchasedBy
apple 7 43% customer-a
orange 3 18% customer-b
orange 4 25% customer-c
apple 2 10% customer-d

productTypesテーブル
productTypeId productname
1 　　　　　　apple
2 　　　　　　orange

purchasedLogテーブル
productTypeId 　　purchased　　　 　　purchasedBy
1 　　　　　　　　2019-1-14 00:00:00 customer-a
2 　　　　　　　　2019-1-14 00:00:00 customer-b

現行スクリプト

with prd_dist as (select ct.products, count(c.productTypeId) as cnt, c.purchasedBy
from PurchasedLog as c join ProductTypes as ct 
on c.productTypeId = ct.productTypeId 
where purchased between '2019-1-14 00:00:00' and '2019-1-15 00:00:00'
group by ct.products, c.purchasedBy),
total_amount as (
select cd.*, sum(cd.cnt) over() as total_cnt 
from prd_dist as cd)
select ta.products, ta.cnt, 100.0*ta.cnt/ta.total_cnt as ratio, ta.purchasedBy from total_amount as ta
order by ta.cnt desc

Answer 1

countは予約語ですので使用しないで下さい。
cntに置き換えて回答します。

select product, sum(cnt) as cnt, customer
form テーブル
group by product, customer

Comments

[minhouse10]

sazi様、ご回答頂きありがとうございます。予約語について今後意識して使わない様に気をつけます。
ただしご教授頂いたクエリーですと、期待した結果を得る事ができませんでした。こちらでも継続調べてまいります。

[sazi]

全く同じSQLで実行していますか？（こんなシンプルなSQLで結果が違うとは考えにくので）
そもそも「sum関数にcase式を組み合わせる」と言われているので別なSQLで実行しているのではありませんか？

[minhouse10]

sazi様、コメント頂きありがとうございます。説明が足りずに申し訳ございません、実際には業務で使っているもう少し複雑な別のSQLクエリーを実行いたしました。アドバイスを仰ぐには実際に実行しているクエリーを乗せるべきでした。必要な情報をマスクしてこちらに掲載できるようにしてみます。

[minhouse10]

sazi様、現在利用しているスクリプトを追記、質問内容を更新させて頂きました。よろしければお手隙の際にご確認頂けますと幸いです。

[sazi]

何がどうあるべきかも伝えずに分かると思いますか？

推測にしかなりませんけど、
select ct.products, count(c.productTypeId) as cnt, c.purchasedBy
from PurchasedLog as c join ProductTypes as ct
on c.productTypeId = ct.productTypeId
where purchased between '2019-1-14 00:00:00' and '2019-1-15 00:00:00'
group by c.productTypeId, ct.products, c.purchasedBy

上記部分で、count(c.productTypeId)の部分がおかしいなら、count(distinct c.productTypeId)としてみてはどうでしょうか。

[minhouse10]

sazi様、アドバイス頂きありがとうございます。distinctも試してみましたが、うまく行きませんでした。無理にSQLクエリー上ではなく後続の処理にて対応できないか検討も致します。

[sazi]

count(項目)は項目がNullの場合にはカウントされないのは理解されていますよね？
単なるjoinだからNullはあり得ませんけど、left joinの結果でカウントしないと駄目なケースの場合には考慮が必要です。

後考えられるのはjoin ではなくてleft joinにした上で distinctすることくらいですね

[minhouse10]

sazi様、アドバイスありがとうございます。left join + distinct でも結果はうまく行きませんでした。テーブルサイズの小さいテスト環境で試行錯誤してみます。

[sazi]

PurchasedLog と ProductTypesのサンプルデータを質問に追記して下さい

[minhouse10]

ありがとうございます。承知いたしました、少々お時間頂ければと存じます。

[Py_pp]

select ct.products, count(c.productTypeId) as cnt, c.purchasedBy
from PurchasedLog as c join ProductTypes as ct
on c.productTypeId = ct.productTypeId
where purchased between '2019-1-14 00:00:00' and '2019-1-15 00:00:00'
group by c.productTypeId, ct.products, c.purchasedBy

の例ですと、group by のc.productTypeIdは不要ではないでしょうか

[sazi]

@Py_ppさん
指摘の通りですね。

[minhouse10]

@Py_ppさん、ご指摘ありがとうございます。sazi様、PurchasedLog と ProductTypesのサンプルデータを追記させて頂きました。

[sazi]

処理後の結果になるサンプルじゃないと意味がありません・・・
そもそもおかしいと言ってるのは演算後のどの項目なんでしょう？

[minhouse10]

ありがとうございます。お手数お掛けしてしまいすいません。処理結果のサンプルの説明文を変更させて頂きました。現行のスクリプト実行結果をCTEで次のSELECT処理に渡して、同じユーザーが同じ品を購入した場合の行を一行に纏められないかと漠然に考えていました。しかしここに記載したスクリプトの内容とサンプルのテーブルデータが実際のものと異なるので、重要な部分省いてしまっているかも知れません、

[Py_pp]

サンプルデータに一番重要な同じユーザーが同じ品を購入したログが省かれているようです。
サンプルデータで動く"現行スクリプトと同じようなサンプルSQL"を作成していただき、実行結果を記載するのが良いと思います。
↑もしかしたらサンプルSQL作成時になにか気付くことがあるかもしれないですよ

[minhouse10]

Py_pp様、アドバイス頂きありがとうございます！仰る通りtmp tablesを作って実際にSQLも別途作成しテストする事を検討します。