mysql - 检查 "working time"用户的服务日志——having 子句中的条件

Question

我有一个按用户 ID 列出的服务使用情况表，并附有一个维度表，其中包括日期、星期几、一天中的小时等。每次用户使用该服务时，都会创建一个新行来记录使用时间。

我希望找到至少在五个工作日中有四个使用该服务的用户，每天至少在一天中至少 8 个不同的小时内记录使用情况。这些是“工作时间”用户。我怎样才能最好地实现这一目标？

目前，我正在使用下面的 having 子句，该子句不能完全捕获上面指定的条件。相反，它让用户在一个月内至少有 16 个工作日使用该服务，并且平均每天使用时间至少为 8 小时。

select 
a.userId
from SERVICE_LOGS_MTHLY a 
inner join DIM_TIME_OF_DAY b
  on a.TOD_ID = b.TOD_ID
having
  count(distinct (case when b.day_of_week in ('MONDAY','TUESDAY','WEDNESDAY','THURSDAY','FRIDAY') then a.day end)) >= 16 
  AND
  count(distinct (case when b.day_of_week in ('MONDAY') then a.day||b.hour24 end)) >= 8*count(distinct (case when b.day_of_week in ('MONDAY') then a.day end))
  AND
  count(distinct (case when b.day_of_week in ('TUESDAY') then a.day||b.hour24 end)) >= 8*count(distinct (case when b.day_of_week in ('TUESDAY') then a.day end))
  AND
  count(distinct (case when b.day_of_week in ('WEDNESDAY') then a.day||b.hour24 end)) >= 8*count(distinct (case when b.day_of_week in ('WEDNESDAY') then a.day end))
  AND
  count(distinct (case when b.day_of_week in ('THURSDAY') then a.day||b.hour24 end)) >= 8*count(distinct (case when b.day_of_week in ('THURSDAY') then a.day end))
  AND
  count(distinct (case when b.day_of_week in ('FRIDAY') then a.day||b.hour24 end)) >= 8*count(distinct (case when b.day_of_week in ('FRIDAY') then a.day end))

Answer 1

这涉及到聚合，即涉及两个步骤。一是提供用户每天登录服务的小时数，二是提供每周工作日的数量。

使用一个名为 Hourly 的简单表，它似乎与您的表的描述最低限度匹配，请参阅我的 SQL Fiddle 场景。

第一步是每天汇总时间。

select  h.ID, h.StartDate, count(*) as DailyCount
from    Hourly  h
where   DoW in( 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday' )
group by h.ID, h.StartDate;

您不太清楚，因此这假设一个条目使用一小时，无论用户在该小时内执行可记录事件多少次。如有必要，请进行调整。

现在只需将每周加起来有多少天为 8 或更多。这使用上面查询的结果，因此将其放入 CTE 中，或者如果您没有可用的 CTE (MySQL)，则从中创建一个 View 。

with
Daily( ID, StartDate, DailyCount )as(
    select  h.ID, h.StartDate, count(*) as DailyCount
    from    Hourly  h
    where   DoW in( 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday' )
    group by h.ID, h.StartDate
)
select  d.ID, Sum( case when DailyCount < 8 then 0 else 1 end ) as WeeklyCount
from    Daily   d
group by d.ID, To_Char( d.StartDate, 'iw' );

现在您只需检查每个用户每周的 WeeklyCount 是否为 4 或更多。

注意:To_Char 允许您按周分组。对于除 Oracle 之外的大多数 DBMS，这是由 DatePart 函数提供的。