PostgreSQL碎碎念
前言
最近在做用户画像功能，对数据库中的用户数据进行分析，写了好多SQL，用到了很多PostgreSQL数据库中奇奇怪怪的语法，现在记录一下。
1、string_to_array结合unnest
适用于一个字段中存放多组数据，通过某个标识符（如","）分隔，但是想把这个数组数据分成多行展示的情况
SQL语句
unnest(string_to_array(字段, ',')) AS industry_id
如果字段为1,2,3，select 字段将会得到
1,2,3
使用SQL语句后将会得到
1
2
3
2、any
使用场景：用于结合场景1，解决一对多的外连接情况
t2.industry = ANY(string_to_array(t1.industry_classification, ','))
3、GREATEST与LEAST
使用场景：求几个数的最大值和最小值，和max，min这种聚合函数不一样，比如我画图的时候，原本的右边界为100，我用max聚合函数获取到到数据库中的最大值大于100，这时候要以这个最大值为准
GREATEST(n1,n2,n3,..........) 获取最大值
LEAST(N1,N2,N3,N4,......) 获取最小值
4、generate_series
使用场景：生成递增序列，比如查看2020年至2025年的用户数据，但是某个用户2023年的数据可能会缺失，如果直接用现有的数据作为左表，统计完的坐标很可能成为
2020 2021 2022 2024 2025,缺少了2023这个坐标。这就需要自己画一个年份序列
生成一个年份序列，看前10年的数据
SELECT generate_series(
(cast(EXTRACT(YEAR FROM CURRENT_DATE) as int) - 9),
cast(EXTRACT(YEAR FROM CURRENT_DATE) as int)) AS YEAR
5、DISTINCT ON
使用场景：比如我需要获取某个用户在某年的最新创建的数据
这里DISTINCT ON (year, user_id)表示获取元组为year,user_id开始的第一条数据，这里的元组必须经过排序，不然会报错，order by通过create_time倒序排列，就可以找到最新的数据
SELECT DISTINCT ON (year, user_id) *
FROM your_table
ORDER BY year, user_id, create_time DESC;
6、在字符串中包含数字，需要截取数字按照数字大小排序，不按照字典序排序
获取字符位置
position('-' in assess_no)
字符串截取
-- 从位置0截取到'-'出现的位置
substr(assess_no, 0, position('-' in assess_no))
-- 从位置'-'出现的位置截取到最后的位置
substr(assess_no, position('-' in assess_no) +1)
