Teradata
需要有關 Teradata 中 SQL 查詢的幫助
我有一個包含以下列的表格
member_number step_count social_activity -- (which contains both Challenges and Posts) high_fives
基本上所有第 2-4 列都是成員的計數。
我想創建一個報告,我可以在其中顯示
member_number
、step_count
、social_activity
(它是“挑戰”)、social_activity
(它是“文章”)和high_fives
。我寫了查詢
select member_number, step_count, social_activity from ABC group by member_number
但我希望查詢獲得
social_activity
(‘challenges’)和social_activity(‘posts’)的單獨計數。有人可以幫我弄這個嗎?
似乎您需要基於 CASE的條件總和:
select member_number, sum(case when social_activity = 'challenges' then step_count else 0 end) as challenges, sum(case when social_activity = 'posts' then step_count else 0 end) as posts from ABC group by member_number
您沒有指定是否要計算 step_count 中的行數或求和。我假設它包含先前計算的計數,因此我使用了 SUM 函式。它與 count() 而不是 sum() 相同。如果您需要將 high_fives 添加為一般總計(在成員級別,而不是在活動類型級別,您需要執行外部查詢。試試這個。這在 SQL Fiddle 中不起作用,因為 MySQL 不支持完全外部連接,但它應該在 Teradata 中工作。
select a.member_number, sum(challenges), sum(posts), sum(high_fives) from ( select a.member_number, Challenges, Posts from (select member_number, sum(step_count) as Challenges from activity where social_activity ='Challenge' group by member_number) a full outer join (select member_number, sum(step_count) as Posts from activity where social_activity ='Post' group by member_number) b on a.member_number = b.member_number group by a.member_number ) a inner join activity b on a.member_number = b.member_number group by a.member_number