sql - 按月，日，小时+间隔和孤岛分组

我需要计算（百分比）状态在白天、小时或月份（工作时间）中的真实时间。
我把桌子简化成这个：

| date                      | status    |
|-------------------------- |--------   |
| 2018-11-05T19:04:21.125Z  | true      |
| 2018-11-05T19:04:22.125Z  | true      |
| 2018-11-05T19:04:23.125Z  | true      |
| 2018-11-05T19:04:24.125Z  | false     |
| 2018-11-05T19:04:25.125Z  | true      |
....

我需要得到结果（取决于参数）这个：
持续数小时：

| date                      | working_time |
|-------------------------- |--------------|
| 2018-11-05T00:00:00.000Z  | 14           |
| 2018-11-05T01:00:00.000Z  | 15           |
| 2018-11-05T02:00:00.000Z  | 32           |
|...                        | ...          |
| 2018-11-05T23:00:00.000Z  | 13           |

几个月：

| date                      | working_time |
|-------------------------- |--------------|
| 2018-01-01T00:00:00.000Z  | 14           |
| 2018-02-01T00:00:00.000Z  | 15           |
| 2018-03-01T00:00:00.000Z  | 32           |
|...                        | ...          |
| 2018-12-01T00:00:00.000Z  | 13           |

我的SQL查询如下所示：

 SELECT date_trunc('month', date)                                         as date,
       round((EXTRACT(epoch from sum(time_diff)) / 25920) :: numeric, 2) as working_time
FROM (SELECT date,
             status as current_status,
             (lag(status, 1) OVER (ORDER BY date)) AS previous_status,
             (date -(lag(date, 1) OVER (ORDER BY date))) AS time_diff
      FROM table
     ) as raw_data
WHERE current_status = TRUE AND previous_status = TRUE
GROUP BY date_trunc('month', date)
ORDER BY date;

工作正常，但速度很慢。对优化有什么想法吗？是否可以使用Row_Number（）函数？

最佳答案

试试这个：

   SELECT t.month_reference as date,
            round(  sum(if(t_aux.status,1,0))  / 25920) :: numeric, 2) as working_time

#我估计你用这个号码是因为系统的正常运行时间是60*18*24，
#如果我想要60*60*24*天内的总秒数（最后一天（t.month\u参考值））的话，我会使用这个

FROM (SELECT date_trunc('month', t.date) as month_reference
          FROM table
         ) as t
    left join table t_aux
    on t.month_reference=date_trunc('month', t_aux.date)

因此，当我们按月份分组时，sum（）将只查找那些为true且具有所引用月份的行

   and t_aux.date <
    (select t1.date
    from table t1
    where t.month_reference=date_trunc('month', t1.date)
    and t1.status=false
    order by t1.date asc limit 1 )

我添加此项，以便它只选择true行，直到在同一个月引用中找到状态为false的行为止

    GROUP BY t.month_reference
    ORDER BY t.month_reference;

关于sql - 按月，日，小时+间隔和孤岛分组，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/53285620/