Teradata

需要有關 Teradata 中 SQL 查詢的幫助

  • September 16, 2015

我有一個包含以下列的表格

member_number
step_count 
social_activity -- (which contains both Challenges and Posts)
high_fives

基本上所有第 2-4 列都是成員的計數。

我想創建一個報告,我可以在其中顯示member_numberstep_countsocial_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

引用自:https://dba.stackexchange.com/questions/115259