PostgreSQL 语句记录

最新推荐文章于 2025-10-24 17:02:39 发布

原创最新推荐文章于 2025-10-24 17:02:39 发布 · 1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql #数据库

sql 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了一系列实用的SQL技巧，包括创建索引以提高查询效率、生成连续日期的方法、使用date_trunc()函数处理时间戳以及如何生成连续日期并进行类型组合。这些技巧对于日常的数据管理和分析工作非常有用。

1、创建索引

-- 创建索引
CREATE SEQUENCE user_ID_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

-- 应用索引
alter table user alter column id set default nextval('user_ID_seq');

2、生成连续日期

-- 生成连续日期
SELECT
	daytime::date date_time 
FROM
	generate_series ( ( SELECT ( now( ) + INTERVAL '-1 month' ) :: DATE ), ( SELECT now( ) :: DATE ), '1 day' ) AS daytime
order by date_time;

3、date_trunc() 函数，用于将时间戳或时间间隔截断为指定的精度级别

-- 获取上月月初日期
select date_trunc('month', now() + '-1 months')::date

-- 获取月初日期
select date_trunc('month', now())

-- 查询上个月数据
date >= date_trunc('month', now() + '-1 months')::date
date < date_trunc('month', now())::date

-- 每月1号 12点
select date_trunc('month',now()) +interval '12 h';

-- 每月15号9点半
select date_trunc('month',now()) + interval '15 d 9 h 30 min';

-- 每天9点	
select date_trunc('day',now()) + interval '9 h';

-- 每周的今天
select date_trunc('day',now()) + interval '7 d';

-- 每周的周二第一分钟
select date_trunc('weak',now())  + interval '1d 1minute';

-- 每小时
select date_trunc('h',now()) + interval '30 minute';

-- 每分钟
select date_trunc('minute',now()) + interval '30 second';

-- 每30分钟
select date_trunc('minute',now()) + interval '30 minute 30 second';

-- 本季度的第15天,15小时 15分 30秒
select date_trunc('quarter',now()) + interval '15 d 15 h 15 minute 30 second';

-- 每个季度最后一天的晚上11点
select date_trunc('quarter',now() ) - interval '1 h';

-- 每个季度的最后一天的晚上的11点(从下个季度开始算起)
select date_trunc('quarter',now() + interval '3 month') - interval '1 h';

4、生成连续日期及将指定类型进行组合，即同一个日期对应多个类型行

-- 将生成的连续日期和类型进行组合成一张表（方法：将日期和类型表进行合并为以逗号拼接成字符串，然后将字符串分割成多行）
SELECT
	dt.date_time,
	UNNEST ( string_to_array( dt."type", ',' ) ) "type"
FROM
	(
	SELECT
		to_char( daytime, 'yyyy-MM-dd' ) date_time,
		( SELECT string_agg ( "type", ',' ) FROM ( SELECT DISTINCT "type" FROM table_1 ) ty ) "type" 
	FROM
		generate_series ( ( SELECT ( now( ) + INTERVAL '-1 month' ) :: DATE ), ( SELECT now( ) :: DATE ), '1 day' ) AS daytime 
	ORDER BY
		date_time 
	) dt

5、