数据聚合

本文介绍了数据聚合的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两个表格需要计算观看百分比.

I have two tables from which I need to calculate viewing percentage.

表 A 有三列

Table A Program_id Viewing_Day Seconds_viewed ------------------------------------- 1 1 520 1 2 330 1 3 650 1 5 100 2 1 90 2 3 80 2 4 560 2 6 980 2 7 1050

表 B 有四列

Table B Program_id Reporting_day Total_Seconds Weight --------------------------------------------- 1 1 1800 1 1 3 1800 0.95 1 5 1800 0.9 1 7 1800 0.8 2 1 3600 1 2 3 3600 0.9 2 5 3600 0.8 2 7 3600 0.7

需要计算每个 Reporting_Day 的观看百分比

Need to Calculate viewing percentage for each Reporting_Day

Viewing_percentage = ( SUM(Seconds_Viewed)/Total_seconds ) * Weight * 100

示例计算Program_id =1和Reporting_Day =5

因此，需要考虑表 A 中 Viewing_Day<=5 for Program_id=1 的所有记录

So, Need to consider all Records for Viewing_Day<=5 for Program_id=1 from Table A

Table A Program_id Viewing_Day Seconds_viewed ------------------------------------- 1 1 520 1 2 330 1 3 650 1 5 100

总秒数为 1600

表2中的对应记录

Table B Program_id Reporting_day Total_Seconds Weight --------------------------------------------- 1 5 1800 0.9

公式化

Viewing_percentage = ( (520+330+650+100)/1800 ) * 100 * 0.9 = 80

我需要计算所有并在最终表 C 中插入结果

I need to calculate for All and Insert Result in Final Table C

Table c Program_id Reporting_day Viewing_percentage -------------------------------------------- 1 1 28.88 - (520/1800) * 100 * 1 1 3 79.16 - (1500/1800) * 100 * 0.95 1 5 80.00 - Explained Above 1 7 71.11 - (1600/1800) * 100 * 0.8 2 1 2.50 - (90/3600) * 100 * 1 2 3 4.25 - (170/3600) * 100 * 0.9 2 5 16.22 - (730/3600) * 100 * 0.8 2 7 53.67 - (2760/3600) * 100 * 0.7

我需要为此编写 SQL.我只能为每个 Reporting_Day 想到 UNION.Reporting_Day 将保持不变 (1,3,5,7)

I need to write SQL for the same. I can only think of UNION for each Reporting_Day. Reporting_Day will remain constant (1,3,5,7)

你能帮忙写下不使用 UNION 的 SQL 吗?硬编码 Reporting_Day (1,3,5,7) 不是问题.

Can you please help in writing SQL which doesn't use UNION ? Hard coding Reporting_Day (1,3,5,7) is not an issue.

推荐答案

如果您只需要计算几个 Reporting_days，那么这应该很顺利:

if you need to calculate only few Reporting_days, then this should go well:

select TableB.Program_Id, TableB.Reporting_Day, case when TableB.Reporting_day = 7 then cast((TableA_TOT.tot_seven/TableB.Total_Seconds) * 100* TableB.Weight as decimal(5,2)) when TableB.Reporting_day = 6 then cast((TableA_TOT.tot_six/TableB.Total_Seconds) * 100* TableB.Weight as decimal(5,2)) when TableB.Reporting_day = 5 then cast((TableA_TOT.tot_five/TableB.Total_Seconds) * 100* TableB.Weight as decimal(5,2)) when TableB.Reporting_day = 4 then cast((TableA_TOT.tot_four/TableB.Total_Seconds) * 100* TableB.Weight as decimal(5,2)) when TableB.Reporting_day = 3 then cast((TableA_TOT.tot_three/TableB.Total_Seconds) * 100* TableB.Weight as decimal(5,2)) when TableB.Reporting_day = 2 then cast((TableA_TOT.tot_two/TableB.Total_Seconds) * 100* TableB.Weight as decimal(5,2)) when TableB.Reporting_day = 1 then cast((TableA_TOT.tot_one/TableB.Total_Seconds) * 100* TableB.Weight as decimal(5,2)) else 0 end weight from TableB inner join ( select TA_TOT.program_id, sum(TA_TOT.tot_seven) tot_seven, sum(TA_TOT.tot_six) tot_six, sum(TA_TOT.tot_five) tot_five, sum(TA_TOT.tot_four) tot_four, sum(TA_TOT.tot_three) tot_three, sum(TA_TOT.tot_two) tot_two, sum(TA_TOT.tot_one) tot_one from ( select TA_grp.program_id, case when TA_grp.seven = '1_7' then sum(TA_grp.Seconds_viewed) else 0 end tot_seven, case when TA_grp.six = '1_6' then sum(TA_grp.Seconds_viewed) else 0 end tot_six, case when TA_grp.five = '1_5' then sum(TA_grp.Seconds_viewed) else 0 end tot_five, case when TA_grp.four = '1_4' then sum(TA_grp.Seconds_viewed) else 0 end tot_four, case when TA_grp.three = '1_3' then sum(TA_grp.Seconds_viewed) else 0 end tot_three, case when TA_grp.two = '1_2' then sum(TA_grp.Seconds_viewed) else 0 end tot_two, case when TA_grp.one = '1_1' then sum(TA_grp.Seconds_viewed) else 0 end tot_one from ( select TableA.Program_id, TableA.viewing_day, TableA.Seconds_viewed, case when TableA.viewing_day <= 7 then '1_7' else '' end seven, case when TableA.viewing_day <= 6 then '1_6' else '' end six, case when TableA.viewing_day <= 5 then '1_5' else '' end five, case when TableA.viewing_day <= 4 then '1_4' else '' end four, case when TableA.viewing_day <= 3 then '1_3' else '' end three, case when TableA.viewing_day <= 2 then '1_2' else '' end two, case when TableA.viewing_day <= 1 then '1_1' else '' end one FROM TableA ) TA_grp group by program_id, five, four, three, two, one ) TA_TOT group by TA_TOT.program_id ) TableA_TOT on TableB.Program_id = TableA_TOT.Program_Id