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

道虽迩，不行不至

已于 2023-06-07 17:22:24 修改

阅读量5.7k

点赞数

分类专栏：数据库文章标签： postgresql sql

于 2023-01-03 10:02:31 首次发布

本文链接：https://blog.csdn.net/zf_csdn123/article/details/128527816

版权

数据库专栏收录该内容

14 篇文章 0 订阅

订阅专栏

获取每月最新一条数据及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

SELECT
	concat ( 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 ) calendars
	LEFT JOIN (
	SELECT COUNT
		( 1 ) num,
		SUBSTRING ( jxsj, 0, 8 ) mon 
	FROM
		"public"."qlb_monitor" 
	WHERE
		jxsj >= TO_CHAR( ( DATE_TRUNC( 'MONTH', CURRENT_DATE ) - INTERVAL '11 MONTH' ), 'YYYY-MM-DD' ) 
	GROUP BY
		SUBSTRING ( jxsj, 0, 8 ) 
	) d ON calendars.mon = d.mon 
ORDER BY
	calendars.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,
		(CASE
			WHEN 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 
	FROM
		gemp_social_security t 
	WHERE
		t.filling_month = '2022-12' 
	ORDER BY
	t.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"
AS
select 
	id,
	name,
	user_no
from 
	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_analasis
after insert or update or delete on system_user
for each statement 
  execute procedure user_analasis_func();

postgresql函数相关操作

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