数据聚合

Question

我有两个表，我需要从其中计算观看百分比。

表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的查看百分比

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

示例计算Program_id =1和Reporting_Day =5的查看百分比

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

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中插入结果

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的联合。Reporting_Day将保持不变(1,3,5,7)

请您帮助编写不使用UNION的SQL吗？硬编码Reporting_Day (1,3,5,7)不是一个问题。

Answer 1

这就是你问题的答案：

SELECT
   program_id, 
   reporting_day, 
   CAST((CAST(total AS FLOAT)/CAST(total_seconds AS FLOAT)*100*weight) AS DECIMAL(5,2))
     AS viewing_percentage
   FROM table_b
        OUTER APPLY (
             SELECT
                  SUM(seconds_viewed) AS total
                  FROM table_a
                  WHERE table_a.program_id = table_b.program_id
                  AND table_a.viewing_day <= table_b.reporting_day
         ) AS t

我使用的环境是Microsoft 2014

Answer 2

如果您只需要计算几个Reporting_days，那么这应该是很好的：

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