Postgresql时间处理技巧，每半天，每周，每月和每5分钟统计

2023-11-01

一、每半天

如果有张表log_bus_runinfo里有一个created_date是timestamp类型，如何统计12点前的数据？

在 PostgreSQL 中，您可以使用 DATE_TRUNC 函数和 WHERE 子句来统计特定时间范围内的数据。以下是一个查询例子，用于统计 log_bus_runinfo 表中创建日期 created_date 早于当天中午 12 点的记录数：

SELECT COUNT(*) 
FROM log_bus_runinfo 
WHERE DATE_TRUNC('day', created_date) = current_date AND created_date < current_date + interval '12 hours'

上述查询会使用 DATE_TRUNC 函数将 created_date 字段截断为该天（时间部分被删除），之后使用 WHERE 子句选择操作当天（而不是整个历史）的记录，并且创建日期小于这一天中午 12 点（即 current_date + interval '12 hours'）。

同样，如果处理后半天的SQL：

SELECT COUNT(*) 
FROM log_bus_runinfo 
WHERE DATE_TRUNC('day', created_date) = current_date 
AND created_date >= current_date + interval '12 hours'

二、每周

如果有张里程表count_mileage，字段count_mileage表示每天的里程数,如何统计上一周的公里数？

SELECT
        created_date::DATE-(extract(isodow from created_date::TIMESTAMP)-1||'day')::interval countWeek,
        sum(mileage_count) mileage
        from count_mileage
        GROUP BY created_date::DATE-(extract(isodow from created_date::TIMESTAMP)-1||'day')::"interval"
        order by countWeek
        limit 1

它用于从count_mileage表中检索日期创建和总路程，并根据学院id和每周计数对其进行分组。以下是逐行的语法分析：

select created_date::date-(extract(isodow from created_date::timestamp)-1||'day')::interval countweek,: 选择created_date列按照给定的方式格式化，从这个处减去星期中第一天前面的日期（例如，今天如果是星期四，则减去3天的时间），并将结果设置为名为countweek的新列。
sum(mileage_count) mileage: 计算mileage_count的总值，并将结果命名为mileage。
from count_mileage: 从count_mileage表中选择数据。
where 1=1: 这个条件没有实际效果，只是作为一个占位符。
group by created_date::date-(extract(isodow from created_date::timestamp)-1||'day')::"interval": 根据日期进行分组，与上面的created_date列的操作一样。
order by countweek: 按名称为countweek列的升序排序。
limit 1: 结果最多返回1周。

总之，这个sql查询检索了给定条件下每周的总里程数，并按日期和计数排序。

如果统计近8周的里程数？SQL是：

select
        created_date::DATE-(extract(isodow from created_date::TIMESTAMP)-1||'day')::interval countWeek,
        sum(mileage_count) mileage
        from count_mileage
        GROUP BY created_date::DATE-(extract(isodow from created_date::TIMESTAMP)-1||'day')::"interval"
        order by countWeek
        limit 8

三、每月

如果有张里程表count_mileage，字段count_mileage表示每天的里程数,如何统计每月的公里数？

SQL:

select distinct to_char(created_date,'yyyy-mm') countMonth ,sum(mileage_count) mileage
        from count_mileage
        where 1=1
        and to_char(created_date,'yyyymm')>to_char((select now() as timestamp)-'6 month'::"interval",'yyyymm')
        group by to_char(created_date,'yyyy-mm')
        order by countMonth

它从count_mileage表中检索日期创建和总里程，并根据每月计数进行分组。以下是逐行的语法分析：

select distinct to_char(created_date,'yyyy-mm') countmonth: 选择created_date列并将其格式化为年月格式。该distinct关键字用来去掉重复行，如果有多个日期值对应同一个年月，则只返回一个。
,sum(mileage_count) mileage: 计算mileage_count的总值，并将结果命名为mileage。
from count_mileage: 从count_mileage表中选择数据。
where 1=1: 这个条件没有实际效果，只是作为一个占位符。
and to_char(created_date,'yyyymm')>to_char((select now() as timestamp)-'6 month'::"interval",'yyyymm'): 仅选择最近六个月内的行，在查询时将当前时间减去6个月的时间来获得区别日期，并按照同样的格式转换成字符串后和表中的字段比较，筛选出符合条件的记录。
group by to_char(created_date,'yyyy-mm'): 根据年月格式对数据进行分组。
order by countmonth: 按名称为countmonth列的升序排序。

因此，这个sql查询检索了过去6个月的每月总里程数，并按时间顺序排序。

四、每5分钟数据

如果有张表是GPS信息，log_bus_driver，其中有经度、纬度字段bus_longitute,bus_latitute，时间是created_date，想按照每5分钟进行分段取信息进行分析，如何做？

 SELECT distinct on (CAST (extract(epoch from date_trunc('second', created_date)) AS integer) / 300) created_date
            ts, CAST (extract(epoch from date_trunc('second', created_date)) AS integer) % 300 as sec,
            bus_longitude, bus_latitude
        from log_bus_drive
        where to_char( created_date, 'yyyyMMdd' ) = '20230428'
        order by CAST (extract(epoch from date_trunc('second', created_date)) AS integer) / 300 asc, created_date asc

这条SQL语句主要功能是从" log_bus_drive" 表中选出所有"created_date"为'20230428' 的记录，并且按照时间间隔5分钟(group by 300 seconds)聚合数据。这里需要对查询语句进行分解来更好地理解它的作用：

以下是每个元素的工作方式:

distinct on (CAST (extract(epoch from date_trunc('second', created_date)) AS integer) / 300)：以5分钟为单位分组并选择每个分组的唯一结果。
created_date ts：该列表示原始的created_date字段值。
CAST(extract(epoch from date_trunc('second', created_date)) AS integer) % 300 as sec：此列显示created_date到该5分钟周期的秒数，range在0-299之间。
bus_longitude and bus_latitude：这些字段包含公交车的经度和纬度坐标。
log_bus_drive: 数据源表。
where to_char( created_date, 'yyyyMMdd' ) = '20230428': 筛选出符合条件的生成日期（created_date）。这里是2023年4月28日。
ORDER BY CAST (extract(epoch from date_trunc('second', created_date)) AS integer) / 300 asc, created_date asc;: 根据时间戳(秒整数)和创建日期进行升序排序。

五、近七日数据统计

如果有张表count_driver_warn,想统计近七日的警告数据，如何做？

select sum(warn_count) warnCount,driver_name driverName
        from count_driver_warn
        where created_date >=CURRENT_DATE- INTERVAL '7 day'
               and driver_name is not null
        group by driver_name
        order by warnCount desc

这个SQL语句的主要功能是从" count_driver_warn" 表中选出所有created_date在7天内的记录，并按照驾驶员名字分组计算每个驾驶员的警告次数数量和警告类型，然后按警告次数从高到低排序。这里需要对查询语句进行分解来更好地理解它的作用：

以下是每个元素的工作方式:

sum(warn_count) warnCount：计算每个驾驶员的总警告次数。
driver_name driverName：数据中包含的驾驶员姓名以及生成搜集日期。
count_driver_warn: 数据源表。
WHERE created_date >= CURRENT_DATE - INTERVAL '7 day' AND driver_name IS NOT NULL：筛选出创建日期在7天内且不为空的驾驶员记录。其中CURRENT_DATE 函数获取当前系统时间， INTERVAL '7 day' 是时间间隔，用来表示最近的7天（从当前日期往前）。
GROUP BY driver_name：将所有驾驶员的记录进行分组，聚合统计每个驾驶员的总警告次数。
ORDER BY warnCount DESC：根据警告次数排序，从高到低按最大警告数排序。

六，前几分钟的数据抓取

SELECT <include refid="Base_Column_List" />
    FROM log_bus_runinfo
    WHERE bus_id = #{busId}
    <if test="beforeMinute !=null">
      and created_date>(now() + ('-'||#{beforeMinute}||' min')::interval)
    </if>
    order by created_date desc
    limit 1

其各部分含义如下：

select <include refid="base_column_list" />：选择出表 log_bus_runinfo 中所有字段。
from log_bus_runinfo：从表 log_bus_runinfo 中进行查询。
where bus_id = #{busid}：限制查询结果中 bus_id 等于传入参数 busid 的那些行。
<if test="beforeminute !=null">...</if>：判断是否需要筛选时间。如果传入参数 beforeminute 不为 null，则进行筛选；否则不进行筛选。
and created_date>(now() + ('-'||#{beforeminute}||' min')::interval)：如果进行了时间筛选，则只选择 created_date 大于当前时间减去参数 beforeminute 所代表分钟数之后的那些记录。
order by created_date desc：按照 created_date 字段逆序排列（即最新的记录排在前面）。
limit 1：仅返回第一条记录作为查询结果。

总体来看，该 sql 语句是基于某个 bus_id 查找其最近的行驶记录，并且可以通过参数 beforeminute 控制所要查找的记录的时间范围。

本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)

数据库