我正試圖為每個id計算用戶下載的不同app的數量，并按類別對該計數進行分組。一個單獨查詢的例子是這樣的：
SELECT id
,COUNT(DISTINCT app) AS gaming_apps
FROM apps_table
WHERE app IN ('Clash of Clans', 'Valorant', 'PUBG')
GROUP BY id
SELECT id
,COUNT(DISTINCT app) AS msg_apps
FROM apps_table
WHERE app IN ('Telegram', 'WhatsApp'/span>, 'Signal'/span>, 'FBMessenger'/span>)
GROUP BY id
SELECT id
,COUNT(DISTINCT app) AS fin_apps
FROM apps_table
WHERE 應用 IN ('Yahoo Finance', 'Robinhood')
GROUP BY id
事實上，我想回傳一個列有id gaming_apps msg_apps fin_apps的表。我想過LEFT JOIN這3個查詢，但我不確定如何做，而且將每個查詢包裝成一個子查詢會太不容易。
SELECT id
,COUNT(CASE WHEN app IN ('Clash of Clans' , 'Valorant'/span>, 'PUBG'/span>) THEN 1 ELSE NULL END) AS gaming_apps
,COUNT(CASE WHEN 應用 IN ('Telegram', 'WhatsApp'/span>, 'Signal'/span>, 'FBMessenger'/span>) THEN 1 ELSE NULL END) AS msg_apps
,COUNT(CASE WHEN 應用 IN ('Yahoo Finance', 'Robinhood') THEN 1 ELSE NULL END) AS fin_apps
FROM apps_table
GROUP BY id
uj5u.com熱心網友回復：
你很接近，你可以在你的查詢中使用SUM而不是count。
SELECT id
,SUM(CASE WHEN app IN ('Clash of Clans' , 'Valorant'/span>, 'PUBG'/span>) THEN 1 ELSE 0 END) AS gaming_apps
,SUM(CASE WHEN 應用 IN ('Telegram', 'WhatsApp'/span>, 'Signal'/span>, 'FBMessenger'/span>) THEN 1 ELSE 0 END) AS msg_apps
,SUM(CASE WHEN 應用 IN ('Yahoo Finance', 'Robinhood') THEN 1 ELSE 0 END) AS fin_apps
FROM apps_table
GROUP BY id
我認為這應該是可行的。如果它不作業，那么，像你說的做一個左鍵連接。
SELECT main.id as id,
game_apps,
msg_apps,
...
FROM apps_table main
LEFT OUTER JOIN (
SELECT id, COUNT(DISTINCT app) AS gaming_apps FROM apps_table WHERE app IN ('Clash of Clans', 'Valorant'/span>, 'PUBG'/span>) GROUP BY id) gaming_apps ON gaming_apps . id=main.id
LEFT OUTER JOIN (
SELECT id , COUNT(DISTINCT app) AS msg_apps FROM apps_table WHERE app IN ('Telegram', 'WhatsApp'/span>, 'Signal'/span>, 'FBMessenger'/span>) GROUP BY id) msg_apps ON msg_apps.id=main.id
...
uj5u.com熱心網友回復：
如果你想要不同的計數，那么使用count(distinct)：
SELECT id,
COUNT(DISTINCT CASE WHEN應用 IN ('Clash of Clans', 'Valorant'/span>, 'PUBG'/span>) THEN app END) as gaming_apps,
COUNT(DISTINCT CASE WHEN應用 IN ('Telegram', 'WhatsApp'/span>, 'Signal'/span>, 'FBMessenger'/span>) THEN app END) as msg_apps,
COUNT(DISTINCT CASE WHEN應用 IN ('Yahoo Finance', 'Robinhood') THEN app END) AS fin_apps
FROM apps_table
GROUP BY id ;
注意，ELSE NULL是多余的，因為NULL是默認值，如果在CASE運算式中沒有匹配。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/334034.html
標籤：
下一篇：ggplot2中的餅圖和百分比