Mysql - 使用 DAYOFWEEK 在特定日期的每小时范围内的行

Question

我正在尝试获取一个查询，该查询将返回一个结果集，其中包含特定小时范围内的读取总数(在 working_hours 表中定义)，具体取决于 DAYOFTHEWEEK 的日期，结果如下:

working | nonworking | weekend | date              | group_id
-----------------------------------------------------------------
50.3    | 30.8       | 0       | 2015-04-01 00:00  | 7
40.3    | 60.8       | 0       | 2015-04-01 00:00  | 8
50.3    | 30.8       | 0       | 2015-04-02 00:00  | 7
40.3    | 60.8       | 0       | 2015-04-02 00:00  | 8

工作和周末范围以working_hours存储在数据库中，隐含非工作时间范围(基本上不在当天的其他范围之间)

表格如下:

读数表包含每小时读数，名为readings

group_id     | reading     | datestamp         
------------------------------------------------------
7            | 30.8        | 2015-04-01 00:00  
7            | 20.2        | 2015-04-01 01:00  
7            | 11.2        | 2015-04-02 00:00  
7            | 20.2        | 2015-04-02 01:00  
8            | 26.2        | 2015-04-01 00:00  
8            | 30.2        | 2015-04-01 01:00  
8            | 26.2        | 2015-04-02 00:00  
8            | 30.2        | 2015-04-02 01:00  

时间范围存储在 working_hours 表中，日期列为 DAYOFTHEWEEK 格式(1 = 星期日，2 = 星期一等):

group_id | day | range_start | range_end | range_type_id | day_type_id 
------------------------------------------------------------------------------
7        | 5   | 08:00:00    | 15:59:00  | 1             | 1
7        | 6   | 00:00:00    | 05:59:00  | 1             | 2
7        | 6   | 06:00:00    | 23:59:00  | 2             | 2
7        | 1   | 00:00:00    | 22:59:00  | 2             | 4
7        | 1   | 23:00:00    | 23:59:00  | 1             | 4

日期类型位于working_hours_day_type表中，对我来说事情变得复杂，工作日和周末只有一个范围，但开始/结束周末有两个范围(“开始周末”第一个范围是工作时间，第二个范围是周末时间，“周末结束”第一个范围是周末时间，第二个范围是工作时间)。

id | type
------------------
1  | Weekday
2  | Start Weekend
3  | Weekend
4  | End Weekend    

范围类型位于working_hours_range_type表中:

id | type
------------------
1  | Working
2  | Weekend

我的Mysql知识仅限于简单的SELECT、INSERT等以及JOIN的基础知识 - 我已经了解了 HOUR(datestamp) BETWEEN 8 AND 14 但不知道如何使用 WHERE datestamp BETWEEN '2015-04-01 00:00:00' AND '2015-04- 在父查询中迭代子查询02 23:59:00' 如果事实上就是这样完成的...

Answer 1

我不太清楚什么是工作时间或非工作时间，但这有效吗？

SELECT
 sum(CASE WHEN rtype.range_type_id = 1 THEN reading ELSE 0 END) AS working
 sum(CASE WHEN rtype.range_type_id = 2 THEN reading ELSE 0 END) AS nonworking
 CASE WHEN r1.daynum in (7,1) THEN 1 ELSE 0 END as weekend
 date(datestamp) as date
 r1.group_id 
FROM
    (SELECT
        group_id,
        reading,
        time(datestamp) as rTime,
        case when weekday(datestamp) = 0 THEN 2 #weekday() monday to working hours monday
            when weekday(datestamp) = 1 THEN 3 
            when weekday(datestamp) = 2 THEN 4 
            when weekday(datestamp) = 3 THEN 5 
            when weekday(datestamp) = 4 THEN 6 
            when weekday(datestamp) = 5 THEN 7 
            when weekday(datestamp) = 6 THEN 1 
        else NULL
        END CASE AS daynum
    FROM readings) AS r1
    LEFT JOIN working_hours w1
        ON (r1.daynum = w1.day) 
         AND (r1.group_id = w1.group_id) 
         AND (r1.rTime BETWEEN w1.range_start AND w1.range_end)
    LEFT JOIN working_hours_day_type dtype 
        ON w1.day_type_id = dtype.id 
    LEFT JOIN working_hours_range_type rtype
        ON w1.range_type_id = rtype.id
GROUP BY
 CASE WHEN daynum in (7,1) THEN 1 ELSE 0 END,
 date(datestamp) as date,
 r1.group_id