将星期几用于DB2中的聚合 [英] Using days of the week for aggregates in DB2

问题描述

我目前有一个查询，该查询从每天写入的表中读取，因此我每天都有总计.

我要尝试的是修改此查询，以便在有意义的情况下可以将星期几作为汇总.

我现在得到的总数是正确的，但是我想将周日至周六用作一周，并有效地说:``如果今天是星期三，则来自tableOne的周日，周一和周二的WeeklyData的总计''/p>

我有这个查询:

  SELECT员工，sum(当category ='Brown'时，daily_total否则为0结束)为DailyBrownNumbers，sum(当category ='Brown'的情况下，weekly_quota否则为0结束)为WeeklyBrownNumbers，CURRENT_DATE作为DATE_OF_REPORT从DailyRecords其中date_of_report> =当前日期按员工分组 

它从该表中读取每日记录，这仍然是我所需要的，但是我想尽可能在​​一周中的那几天添加针对total_total的总和.

这可以用DB2完成吗?

解决方案

您可以使用dayofweek函数.SQL获取从星期日开始的所有记录，包括当前日期.第二列"DailyBrownNumbers"使用案例语句将总数限制为当前日期记录.第三列"WeeklyTotal"具有从星期日开始的所有记录的总数.

  SELECT员工，sum(当category ='Brown'并且date_of_report> =当前日期时的情况然后daily_total否则0结束)作为DailyBrownNumbers，sum(类别='棕色'的情况然后daily_total否则0结束)为WeeklyTotal，sum(类别='棕色'的情况然后weekly_quota否则0结束)为WeeklyBrownNumbers，CURRENT_DATE作为DATE_OF_REPORT从DailyRecords其中date_of_report> ==(当前日期-(dayofweek(当前日期)-1)天)按员工分组 

I currently have a query that reads from a table which is written to daily, so I have totals for every day.

What I'm trying to do is modify this query so that I can use days of the week as an aggregate, if that makes sense.

THe totals I get right now are correct, but I want to use Sunday through Saturday as a week and effectively say ``'If today is wednesday, sum totals for weeklyData for Sunday, MOnday and Tuesday from tableOne```

I have this query:

SELECT employee,
   sum(case when category = 'Brown' then daily_total else 0 end) as DailyBrownNumbers,
   sum(case when category = 'Brown' then weekly_quota else 0 end) as WeeklyBrownNumbers,
   CURRENT_DATE as DATE_OF_REPORT
from dailyRecords
  where date_of_report >= current_date
group by employee

which reads from that table for the daily records and that's what I need still, but I want to add a sum for daily_total based on those days of the week if possible.

Can this be done with DB2?

解决方案

You can use dayofweek function. SQL gets all records starting from Sunday including current date. Second column "DailyBrownNumbers" uses case statement to restrict totals to current date records. Third column "WeeklyTotal" has totals for all records from Sunday.

SELECT employee,
sum(case when category = 'Brown' and  date_of_report >=  current date 
       then daily_total 
       else 0 end) as DailyBrownNumbers,
sum(case when category = 'Brown' 
       then daily_total 
       else 0 end) as WeeklyTotal,
sum(case when category = 'Brown' 
       then weekly_quota 
    else 0 end) as WeeklyBrownNumbers,
CURRENT_DATE as DATE_OF_REPORT
from dailyRecords
where date_of_report >= ( current date - ( dayofweek(current date) - 1 ) days )
group by employee

这篇关于将星期几用于DB2中的聚合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！