分时数据统计的实现_分时统计-CSDN博客

本文链接：https://blog.csdn.net/bennybi/article/details/127143594

说明：

需求:

思路:

法1：根据n, 求出其中的时间点集合，然后根据时间段进行统计汇总

法2：数据按时间聚合交给Mysql/Mariadb去做, group by 时间段函数（Te - 间隔秒数intervalSecs * floor((Te-Ts) / 间隔秒数intervalSecs) ）

法3: 综合法1和法2，先生成数据格子，用法2方式生成结果后，填充匹配，查询近14天数据示例：

参考:

说明：

按时段统计数据是常见的需求，以下分享下我的实现例子。

需求:

- 有一个(如下图)的环境数据采集表，要求按就近5分钟/x分钟/x小时/x日时间区间内，获取5个平均时间点的温度最高值(temperature)，作为Api数据输出，给前端统计组件使用。

结构：

device_code	设备码
stime	时间戳
temperature	温度
humidity	湿度
created_at	建立时间
updated_at	更新时间

思路:

假设统计的时间段为 Ts 到 Te (Te为当前时间, Ts = Te-时间差), 中间均分n个时间点(t0,t1...tn-1)

法1：根据n, 求出其中的时间点集合，然后根据时间段进行统计汇总

[
    '2022-09-24 18:14:00',
    '2022-09-24 18:08:00',
    '2022-09-24 18:02:00',
    '2022-09-24 17:56:00',
    '2022-09-24 17:50:00',
]

优点：逻辑简单易懂

缺点：要执行n个统计sql语句, 数据难以分页或排序，不够灵活。

**法2：数据按时间聚合交给Mysql/Mariadb去做, group by 时间段函数（Te - 间隔秒数intervalSecs * floor((Te-Ts) / 间隔秒数intervalSecs) ）**

# sql:
SELECT MAX(temperature) temperature, FROM_UNIXTIME(unix_timestamp('2022-10-08 00:00:00') - 518400 * (floor((unix_timestamp('2022-10-08 00:00:00') - unix_timestamp(`created_at`))/518400))) groupTimeFmt FROM `c2_iot_env_factor` WHERE (`device_code`='e831cd73f920') AND (`created_at` BETWEEN '2022-09-08 00:00:00' AND '2022-10-08 00:00:00') GROUP BY `groupTimeFmt` ORDER BY `groupTimeFmt` DESC LIMIT 5

# 表达式：
$query->select(new Expression("MAX(temperature) temperature, FROM_UNIXTIME(unix_timestamp('{$periodTime['end_at']}') - {$intervalSecs} * (floor((unix_timestamp('{$periodTime['end_at']}') - unix_timestamp(`created_at`))/{$intervalSecs}))) groupTimeFmt"));

结果:

[
    '2022-10-04 00:00:00' => [
        'temperature' => '29.13',
        'groupTimeFmt' => '2022-10-04 00:00:00',
    ],
    '2022-09-26 00:00:00' => [
        'temperature' => '30.20',
        'groupTimeFmt' => '2022-09-26 00:00:00',
    ],
]

优点：一条sql，由数据库优化查询，使用灵活可分页排序，参数可控

缺点: 返回数据不规整，有可能有，也有可能无

法3: 综合法1和法2，先生成数据格子，用法2方式生成结果后，填充匹配，查询近14天数据示例：

输入条件

[
'start_at' => '2022-09-24 00:00:00',
'end_at' => '2022-10-08 00:00:00',
]

时间数据格子

[
    '2022-10-08 00:00:00',
    '2022-10-06 00:00:00',
    '2022-10-04 00:00:00',
    '2022-10-02 00:00:00',
    '2022-09-30 00:00:00',
    '2022-09-28 00:00:00',
    '2022-09-26 00:00:00',
]

法2 结果数据

[
    '2022-10-04 00:00:00' => [
        'temperature' => '29.13',
        'groupTimeFmt' => '2022-10-04 00:00:00',
    ],
    '2022-09-26 00:00:00' => [
        'temperature' => '30.20',
        'groupTimeFmt' => '2022-09-26 00:00:00',
    ],
]

返回Api数据，前端渲染

[
    'categories' => [
        '2日',
        '4日',
        '6日',
        '8日',
        '10日',
        '12日',
        '14日',
    ],
    'series' => [
        [
            'name' => '温度',
            'data' => [
                null,
                null,
                '29.13',
                null,
                null,
                null,
                '30.20',
            ],
        ],
    ],
]

完毕。