按月、日统计查询数据SQL、以及case when的使用 -- postgresql、MySQL

postgresql sql 2023-09-02 11:09:17 191人浏览安东尼

摘要

目录获取每月最新一条数据及case when的使用，以及其他（数据类型转换、分页）等使用 - postgresql根据月份分组、创建时间排序，获取排序后的第一条数据。即获取每月最新一条数据

获取每月最新一条数据及case when的使用，以及其他（数据类型转换、分页）等使用 - postgresql

postgresql手册中记录了所有的操作命令级函数，具体可点击下面链接地址查看。
postgresql 手册地址：地址链接

根据月份分组、创建时间排序，获取排序后的第一条数据。即获取每月最新一条数据 - postgresql

PARTITioN BY 分组字段
ORDER BY 排序字段
WHERE s.rn = 1 获取分组排序后的第一条数据
比如数据按月分组，然后按创建时间倒序排序，再获取第一条数据，即获取每月最新的一条数据。

select s.* from (select t.*, row_number() OVER (PARTITION by t.filling_month ORDER BY t.create_time desc) as rows from test t) s  where s.rows = 1

查询显示当前月往前12个月份的数据，可以根据需要查询天或月 - postgresql

generate_series 是以步长为节点拆分时间（格式：generate_series（‘startDate’, ‘endDate’, ‘拆分步长’）），INTERVAL为日期计算，在INTERVAL前加计算符号，后跟要计算加或减的日期。

SELECT concat( EXTRACT ( month from to_date(calendars.mon, 'yyyy-mm')), '月') mons, social_3.alarms_num AS monthCount  FROM ( SELECT to_char( months, 'yyyy-mm' ) mon FROM generate_series ( now( ) - interval '11 mon', now( ), '1mon' ) months ) calendars left join( select s.* from (select t.*, row_number() OVER (PARTITION by t.filling_month ORDER BY t.create_time desc) rn from gemp_social_security t) s  where s.rn = 1 ) social_3 on calendars.mon = social_3.filling_month order by calendars.mon

在这里插入图片描述

##查询当前往前12个月的年月日期SELECT to_char( months, 'yyyy-mm' ) mon FROM generate_series ( now( ) - INTERVAL '11 mon', now( ), '1mon' ) months ##查询当前往前30天的日期 SELECT to_char( months, 'yyyy-mm-dd' ) mon FROM generate_series ( now( ) - INTERVAL '30 day', now( ), '1day' ) months

查询结果如下：
在这里插入图片描述

根据时间统计数据，按月统计，查询每个月份的统计数据，没有数据的月份显示为0 - postgresql

SELECTconcat ( EXTRACT ( MONTH FROM to_date( calendars.mon, 'yyyy-mm' ) ), '月' ) mon,COALESCE ( d.num, 0 ) AS num FROM( SELECT to_char( months, 'yyyy-mm' ) mon FROM generate_series ( now( ) - INTERVAL '11 mon', now( ), '1mon' ) months ) calendarsLEFT JOIN (SELECT COUNT( 1 ) num,SUBSTRING ( jxsj, 0, 8 ) mon FROM"public"."qlb_monitor" WHEREjxsj >= TO_CHAR( ( DATE_TRUNC( 'MONTH', CURRENT_DATE ) - INTERVAL '11 MONTH' ), 'YYYY-MM-DD' ) GROUP BYSUBSTRING ( jxsj, 0, 8 ) ) d ON calendars.mon = d.mon ORDER BYcalendars.mon

Mysql 查询按月统计数据，统计当前日期往前12个月的数据，也可统计天的数据。当月没有数据统计为0

SELECT   days.days,  COUNT(a.id) AS num FROM  (SELECT     DATE_FORMAT( @date := DATE_ADD(@date, INTERVAL + 1 MONTH), '%Y-%m' ) days   FROM    (SELECT       @date := DATE_ADD((SELECT DATE_FORMAT( (SELECT DATE_SUB( CURDATE(), INTERVAL DAYOFYEAR(NOW()) - 1 DAY )), '%Y-%m-%d' )), INTERVAL - 1 MONTH)     FROM      `user`     LIMIT 12) TIME) AS days   LEFT JOIN `test` a     ON a.month = days.days GROUP BY days.days

查询结果如下：
在这里插入图片描述

postgresql case when使用

case when 是作为条件判断使用的函数，在when后写判断条件，多个判断条件可以用and 或 or 进行连接，每个判断when都会产生一个结果then，在then后加when判断通过后对应的返回结果，返回结果类型需是一个字符串类型的结果，否则会提示异常，else是最后的其他结果，通if条件判断的else相同，返回前面所有判断不通过后的一个返回值，最后需要加上end，表示case when的结束。

SELECT t.traffic_accident AS trafficAccident,(CASEWHEN t.traffic_accident_ratio = 0 THEN '0%' WHEN t.traffic_accident_ratio < 0 THEN concat ( t.traffic_accident_ratio, '%' ) ELSE concat ( '+', t.traffic_accident_ratio, '%' ) END ) AS trafficAccidentRatio,concat ( t.injured_ratio, '%' ) AS injuredRatio,t.commonly_accident AS commonlyAccident,concat ( t.loss_ratio, '%' ) AS lossRatio,t.serious_accident AS seriousAccident FROMgemp_social_security t WHEREt.filling_month = '2022-12' ORDER BYt.create_time DESC LIMIT 1

字段值判空条件函数使用

MySQL
MySQL提供了ifnull(字段，‘0’) 函数，如果字段值为null，则返回第二个参数值，同时也提供了if(a > b, ‘a’, ‘b’)函数，条件判断a > b 条件返回true，则返回第二个参数值，否则返回第三个参数值。

postgresql
postgresql同样提供了类似的函数，COALESCE(字段， ‘0’）如果字段值为null，则返回第二个参数值。同样可以使用上面的case - when进行条件判断做相应的返回值处理。

postgresql 字段类型转换

#将字段转为字符类型select cast(t.num as varchar) as num from user;#将字段转为数字类型select cast(t.num as numeric) as num from user;

postgresql 分页

LIMIT和OFFSET允许你只检索查询剩余部分产生的行的一部分；分页使用limit 表示返回查询结果的条数，offset 表示跳过多少条。两个关键字可以单独使用，也可以组合使用，单独使用limit表示返回查询结果的固定条数，单独使用offset表示跳过查询结果的固定条数，然后返回剩余的结果。组合使用如下：表示跳过4条，返回查询结果的4条记录。

select * from user limit 4 offset 4;

SQL查询排序 – 将指定值的数据排序在前边

select id,name from table_name order by case when(name='张三' or name='李四') then 0 else 1 end, id desc

查询生成一列作为排序自增序号

select row_number() over (order by pre desc) as rows, id, name from user ;

postgresql查询一串字符串根据指定字符分割查询显示为多行

##这里根据“,”分割select unnest(string_to_array('张三,李四,王五',',')) as name;

postgresql创建物理视图

物理视图与普通视图的区别是物理视图可以创建索引；

--创建物理视图CREATE MATERIALIZED VIEW "public"."table_view"ASselect id,name,user_nofrom user;--给物理视图创建唯一性索引   id是视图索引字段CREATE UNIQUE INDEX "index_id" ON "public"."table_view" USING btree (  "id" COLLATE "pg_catalog"."default" "pg_catalog"."text_ops" ASC NULLS LAST);

postgresql Use the 24-hour clock, or give an hour between 1 and 12. 问题解决

出现此问题是因为数据中的时间是24小时制，查询默认用的是12小时制，导致时间字段超出限制。
只需要将日期字段查询换成查24小时制即可。
如：

select to_timestamp(create_time, 'yyyy-mm-dd hh24:mi:ss') as creat_time from user;

postgresql - 触发器相关操作

--查看现有触发器select * from pg_trigger;--创建一个触发器  当表system_user表数据变动的时候自动触发更新 user_materialized_view 物化视图数据，函数在后面有写create trigger tri_user_analasisafter insert or update or delete on system_userfor each statement   execute procedure user_analasis_func();

postgresql函数相关操作

--创建一个函数CREATE OR REPLACE FUNCTION "public"."user_analasis_func"()  RETURNS "pg_catalog"."trigger" AS $BODY$ declarebegin   refresh materialized view concurrently user_materialized_view with data;   return null; end; $BODY$  LANGUAGE plpgsql VOLATILE  COST 100