带有4 Shift的Server移位报告

Question

我有SQL，它有耗电量的数据。现有列:日期、名称和值。以下是一个示例：

    DT                      Tag                           Value

2018-09-13 17:09:32.553 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125
2018-09-13 18:09:32.557 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125
2018-09-13 19:09:32.567 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125
2018-09-13 20:09:32.580 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125
2018-09-13 21:09:32.583 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125
2018-09-13 22:09:32.593 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125
2018-09-13 23:09:32.597 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125
2018-09-14 00:09:32.603 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125
2018-09-14 01:09:32.603 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125
2018-09-14 02:09:32.603 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125
2018-09-14 03:09:32.610 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125
2018-09-14 04:09:32.627 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125
2018-09-14 05:09:32.640 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125
2018-09-14 06:09:32.643 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125
2018-09-14 07:09:32.650 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125
2018-09-14 08:09:32.663 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125
2018-09-14 09:09:32.677 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125
2018-09-14 10:09:32.677 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125
2018-09-14 11:09:32.677 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125
2018-09-14 12:09:32.680 Siemens.MDBPLC.elDCS2.elDCS2p   30559.01953125

值每小时插入到表中。

我需要创建轮班报告，在这里我们可以看到哪个班次消耗了每小时的平均能量。我们有4班：

2019-09-13 07:00-19:00 A
2019-09-13 19:00-07:00 B
 2019-09-14 07:00-19:00 C
2019-09-14 19:00-07:00 D

因此，我需要这样的输出：

Date               Shift  Value
2019-09-13 07:00     A     30559.01953125
2019-09-13 19:00     B     30559.01953125
2019-09-14 07:00     C     30559.01953125

他们使用MS 2012。不能更改结构或任何东西，只能从DB中选择数据。

Answer 1

下面我选择了使用两个交叉应用操作符来形成4班的循环，这样列别名就可以被重复使用，希望它更容易阅读。

使用datediff(小时，.)模数从2018-01-01 :00开始，我们可以计算出第一班或第二班的几个小时，然后第二次模数计算告诉我们，这2天中哪一天是相关的，然后我们就可以进行为期2天的4个轮班周期。

select
     ca2.shift_start
   , ca2.shift
   , avg(value) avg_value
from table1
cross apply (
    select
           datediff(hour,'20180101 07:00', dt) % 24 hr
        , (datediff(hour,'20180101 07:00', dt) / 24) % 2 dy
    ) ca1
cross apply (
   select case
          when ca1.dy = 0 and ca1.hr between 0  and 11 then 'a'
          when ca1.dy = 0 and ca1.hr between 12 and 23 then 'b'
          when ca1.dy = 1 and ca1.hr between 0  and 11 then 'c'
          when ca1.dy = 1 and ca1.hr between 12 and 23 then 'd'
          end as shift
        , convert(varchar(13),dateadd(hour,-(case when hr < 12 then hr else hr-12 end),dt),121) shift_start
    ) ca2
group by
     ca2.shift_start
   , ca2.shift
order by
     ca2.shift_start
   , ca2.shift


+----+---------------+-------+----------------+
|    |  shift_start  | shift |   avg_value    |
+----+---------------+-------+----------------+
|  1 | 2018-09-13 07 | c     | 30559,01953125 |
|  2 | 2018-09-13 19 | d     | 30559,01953125 |
|  3 | 2018-09-14 07 | a     | 30559,01953125 |
+----+---------------+-------+----------------+

请参阅：http://rextester.com/VHQK56298

Answer 2

您有定义班次的表吗？在这里，我假设您有一个和日期范围定义与您的样本数据匹配(2018年)。

;with  
shift_table as
(
    select shift_start = '2018-09-13 07:00', shift_end = '2018-09-13 19:00', shift = 'A'    union all
    select shift_start = '2018-09-13 19:00', shift_end = '2018-09-14 07:00', shift = 'B'    union all
    select shift_start = '2018-09-14 07:00', shift_end = '2018-09-14 19:00', shift = 'C'    union all
    select shift_start = '2018-09-14 19:00', shift_end = '2018-09-15 07:00', shift = 'D'
)
SELECT  s.shift_start, s.shift, Value = avg(t.Value)
FROM    sample_table t
        inner join shift_table s    on  t.DT    >= s.shift_start
                                   and  t.DT    <  s.shift_end
group by s.shift_start, s.shift