前言
可以将本次讨论看成一个对PG函数的简单科普，旨在介绍一些实用而不常用的PG函数，起到在实际生产中简化代码的效果
PG的聚集函数
首先要讨论的是PG的聚集函数。何谓聚集函数，官方定义是通过处理一系列数据得出一个输出结果的函数。在通用场景下可以简单的理解为能和group by配合使用的函数。pg的聚集函数分为两类，通用聚集函数和统计用聚集函数，这里我们只讨论第一种
count
估计这个函数是大家最熟悉的聚集函数之一，这里单独拿出来说是因为count有些小陷阱。比如count(*)和count(字段名)在很多时候返回的结果是不一样的，大家需要根据自己的应用场景选择合适的count函数
count(*)：返回结果集的行数，不管这行是不是null
count(1)：和count(*)基本没区别，92之前都是扫描全表的，92之后增加了index only scan一般会变成扫主键索引，如果没有主键或者是表的列很多的情况下，count(1)快一些，因为不同考虑表的全部字段
count(field)：返回数据表中指定字段值不等于null的行数
ly_test=# select * from test;
id | a | b
----+-----+-----
1 | aaa | bbb
| |
2 | |
1 | aaa |
(4 rows)
ly_test=# select count(*) as all,count(id) as id_count from test;
all | id_count
-----+----------
4 | 3
(1 row)
array_agg
array_agg(expression)：将所有输入变成一个数组输出
这是个非常有用的函数，尤其在配合group by使用时。当我们想输出分组之后的某一个字段的所有值的时候非常方便
举个例子：我想知道id是3和5的两个供应商都包含哪些类型的产品
传统的写法可能是这样：
select supplier_id,product_type_name
from b2c_order
where supplier_id in (3,5)
order by supplier_id;
supplier_id | product_type_name
-------------+-------------------
3 | 港澳台
3 | 港澳台
3 | 港澳台
3 | 自由行境外长线
看起来一点都不直观，那么改良一下，用array_agg来写：
select supplier_id,array_agg(product_type_name)
from b2c_order
where supplier_id in (3,5)
group by supplier_id;
supplier_id | array_agg
-------------+-------------------------------------------------------
3 | {港澳台,跟团游境外长线}
5 | {周边游,NULL,周边游,周边游,门票,周边游,周边游,周边游}
结果变得比较直观呃，但是有个问题，数组里有重复数据，比如5号供应商，周边游出现了很多次。ok，这个好办，array_agg支持distinct，我们再次把sql改良成：
select supplier_id,array_agg(distinct product_type_name)
from b2c_order
where supplier_id in (3,5)
group by supplier_id;
supplier_id | array_agg
-------------+-------------------------------------------------------------------------------------------------
3 | {周边游,港澳台,自由行境外长线,跟团游境内短线,跟团游境内长线,跟团游境外短线,跟团游境外长线,NULL}
5 | {周边游,门票,NULL}
结果看起来舒服多了。
array_agg还有很多有用的变体。
比如string_agg(expression,delimiter)，直接返回一个用指定分隔符分割的字符串，便于展示
json_agg(record)，返回一个json数组，便于解析
xmlagg(expression)，返回一个xml
sum
group by 搭配sum能统计分组以后的某个字段的和，但是如果我想统计分组以后某个字段符合某些条件的和呢，这就需要搭配case when使用了
比如，我想统计tts中各大区所有的下单数和已支付订单数,可以这么写
select area,count(1) as 订单数,
sum(case when pay_time is not null then 1 else 0 end) as 已支付订单数
from table
group by area
从这个应用场景发散一下，我们可以看到，计算某个行为在某场景下出现多少次时，通常能这么做
sum(case when <condition> then 1 else 0 end)
max/min
在优化慢query的时候，曾经看到log中连续出现了很多次select adult_price from b2c_product_summary where product_id=XXX order by adult_price limit 1这样的调用，每次传入的product_id都不一样，于是我很好奇这是要干什么啊，问了一下作者，果然是想取每个产品的最低价的那个团期。但其实这个用min()函数配合group by是分分钟就搞定的，你可以写成
select product_id,min(adult_price) from b2c_produc_team where product_id in (XXX,XXX) group by product_id
当然，更多的时候max和min函数被我用作干这些事情，比如我已知某个表有很多数据，但是就想知道它具体有多少数据，我们可以近似的用max(id)来代替count(1)看数据量，前提是这个表的id基本是连续的
b2c_product=# select count(1) from b2c_order_log;
count
---------
2734930
(1 row)
Time: 386.685 ms
b2c_product=# select max(id) from b2c_order_log;
max
---------
2736784
(1 row)
Time: 0.653 ms
或者是这个表有很多重复数据，我需要将重复的删除
delete from test where id not in (select max(id) from test group by XXX)
XXX是能够唯一标识一条数据的条件
无往不利的浮窗函数/window function
定义
众所周知，使用group by的时候只能输出group by的字段和聚集函数生成的汇总数值，这个限制导致很多统计非常不方便
可爱的oracle最早提出了window function的概念，它能在一系列和当前row相关的数据上进行计算然后给出一个输出值，就像通用聚集函数做的那样；但是又不会将已有的row像group by一样进行归一处理。这使得我们能除了能获取当前结果集中的row之外还能获取更多的汇总信息。
window function的格式如下function_name ( * ) OVER ( window_definition )
其中window_definition部分如下
[ existing_window_name ]
[ PARTITION BY expression [, ...] ]
[ ORDER BY expression [ ASC | DESC | USING operator ]
[ NULLS { FIRST | LAST } ] [, ...] ]
[ frame_clause ]
还是用几个例子来说明window function到底是一个什么东东吧
case 1
假设有这样一张学生成绩表，我想知道每个学生的成绩以及它和本组平均成绩的差别
ly_test=# select * from t;
id | groups | score
----+--------+-------
1 | a | 85
2 | a | 80
3 | a | 69
4 | a | 90
5 | b | 77
6 | b | 58
7 | b | 95
(7 rows)
当然我可以很容易的计算出每组的平均成绩
ly_test=# select groups,avg(score) from t group by groups;
groups | avg
--------+---------------------
b | 76.6666666666666667
a | 81.0000000000000000
(2 rows)
但是必须用join才能和上面的成绩明细联系起来，有了window function，这个统计就可以写成这样了
ly_test=# select *,avg(score)over(partition by groups) from t;
id | groups | score | avg
----+--------+-------+---------------------
1 | a | 85 | 81.0000000000000000
2 | a | 80 | 81.0000000000000000
3 | a | 69 | 81.0000000000000000
4 | a | 90 | 81.0000000000000000
5 | b | 77 | 76.6666666666666667
6 | b | 58 | 76.6666666666666667
7 | b | 95 | 76.6666666666666667
(7 rows)
自己的成绩和本组平均成绩的差距一目了然
从上面的例子可以看出，window function最大的好处是可以将聚集函数和表的普通字段一起输出而不用搭配group by关键字。
再来看几个window function的常用情景
case 2
这次我们需要在输出成绩明细的时候同时输出全组的平均分数和每个学生的成绩在本组的排名
ly_test=# select *,avg(score) over w,rank() over w
from t
window w as(partition by groups order by score desc) ;
id | groups | score | avg | rank
----+--------+-------+---------------------+------
4 | a | 90 | 90.0000000000000000 | 1
1 | a | 85 | 87.5000000000000000 | 2
2 | a | 80 | 85.0000000000000000 | 3
3 | a | 69 | 81.0000000000000000 | 4
7 | b | 95 | 95.0000000000000000 | 1
5 | b | 77 | 86.0000000000000000 | 2
6 | b | 58 | 76.6666666666666667 | 3
这里有两点需要注意
1、 window function除了实现group by的功能以外还能附加order by，这个功能是它可以用以一些统计排序
2、如果一条语句中有多个依赖window function的聚集函数，可以在后面先给window做个定义，
然后起好别名，这样能避免多次重复写很长的window定义，
比如上例中的{color:red}window w as(partition by groups order by score desc){color}
case 3
如果我想知道每组有多少个学生，以及该组人数在总人数中的占比，可以用下面这种方法
ly_test=# select distinct groups,
count(1) over (partition by groups),count(1) over()
from t;
groups | count | count
--------+-------+-------
b | 3 | 7
a | 4 | 7
注意第二个window，里面是没有填写分组信息的，这种情况下window function的作用域是整个结果集
case 4
最后说一个我个人觉得特别有用的window function的小函数吧，lag和lead函数，他们的作用是求window function作用域下，指定步长前/后偏移量的数值
举例来说，我想知道所有学生的成绩明细，同时要显示按从高到低顺序同组下一名的成绩
ly_test=# select *,
lead(score,1) over w,
lag(score,1)over w
from t
window w as (partition by groups order by score desc)
order by groups,score desc;
id | groups | score | lead | lag
----+--------+-------+------+-----
4 | a | 90 | 85 |
1 | a | 85 | 80 | 90
2 | a | 80 | 69 | 85
3 | a | 69 | | 80
7 | b | 95 | 77 |
5 | b | 77 | 58 | 95
6 | b | 58 | | 77
因为这里我是按照程序倒排的，所以想要知道成绩低它一名的要用lead函数，高它一名的要用lag函数
结论
window function能和所有已知的聚集函数搭配，就想和group by 搭配一样，不但如此，PG还允许用户定义自己的聚集函数，这样灵活性就更高了。
一些有用又生僻的数据类型
json
pg93开始内置了json处理函数，但其实从92开始pg就支持json数据类型了，只是那时候相应的处理函数是以第三方扩展的形式支持的。
必须要说的是，即使是93中pg内置的json函数也不是很好用，只能说勉强处理一下吧，但是作为一种更通用的数据类型，json显然比hstore更容易理解和使用
说了json的好处也要说说他的坏处，坏处就是这个东西不支持索引，也就是说想要用json上生产暂时还有待商榷。或许可以用来存储一些比较简单的规则类的文本吧。
来看一些实际处理的例子吧。tts中有些活动规则是用json存储的，比如下面这个例子
b2c_product=# select jsonstr from b2c_activities where id=4;
jsonstr
---------------------------
{ +
"name":"游轮立减", +
"description":"游轮立减",+
"leastAdultNumber":2, +
"rules":[{ +
"lowerPay":400000, +
"reduceMoney":80000, +
"day":5, +
"night":4 +
},{ +
"lowerPay":500000, +
"reduceMoney":100000, +
"day":6, +
"night":5 +
},{ +
"lowerPay":500000, +
"reduceMoney":100000, +
"day":7, +
"night":6 +
},{ +
"lowerPay":300000, +
"reduceMoney":60000, +
"day":4, +
"night":3 +
}] +
}
规则很复杂，那么现在我想解析出邮轮立减的每一档优惠金额是多少，该怎么做呢
分析结构可以看到，这个优惠金额存储在rules这个key对应的数组里，我先把这个数组解析出来
b2c_product=# select jsonstr::json#>array['rules'] from b2c_activities where id=4;
?column?
-----------------------
[{ +
"lowerPay":400000, +
"reduceMoney":80000, +
"day":5, +
"night":4 +
},{ +
"lowerPay":500000, +
"reduceMoney":100000,+
"day":6, +
"night":5 +
},{ +
"lowerPay":500000, +
"reduceMoney":100000,+
"day":7, +
"night":6 +
},{ +
"lowerPay":300000, +
"reduceMoney":60000, +
"day":4, +
"night":3 +
}]
(1 row)
然后我们可以看到，这个数组有四个元素，每个元素对应一个档位，目前他们作为一个json_array整体返回的，而我想要做的是把他们分解成每个档位一行
b2c_product=# select json_array_elements(jsonstr::json#>array['rules']) from b2c_activities where id=4;
json_array_elements
-----------------------
{ +
"lowerPay":400000, +
"reduceMoney":80000, +
"day":5, +
"night":4 +
}
{ +
"lowerPay":500000, +
"reduceMoney":100000,+
"day":6, +
"night":5 +
}
{ +
"lowerPay":500000, +
"reduceMoney":100000,+
"day":7, +
"night":6 +
}
{ +
"lowerPay":300000, +
"reduceMoney":60000, +
"day":4, +
"night":3 +
}
(4 rows)
ok做到了，最后我需要把每一档的优惠金额解析出来
select (aaa).key,(aaa).value from (select json_each(json_array_elements(jsonstr::json#>array['rules'])) as aaa from b2c_activities where id=4) tmp;
key | value
-------------+--------
lowerPay | 400000
reduceMoney | 80000
day | 5
night | 4
lowerPay | 500000
reduceMoney | 100000
day | 6
night | 5
lowerPay | 500000
reduceMoney | 100000
day | 7
night | 6
lowerPay | 300000
reduceMoney | 60000
day | 4
night | 3
好了，所有股则成功解析成了一张表，你现在可以用它去增删改查，各种join了，不够回头看看过程，啰嗦啊
这里，我们是一步一步逐层解析的，这种方法适用于json结构不甚明确的情况，如果已知某个值的存储路径，我们完全可以通过path来解析，比如
select jsonstr::json#>array['rules','1','day'] from b2c_activities where id=4;
这里我取出了rules这个key对应的数组中第一组规则里day这个关键字对应的值，取值方法非常像xpath的解析
讲到这里大家会发现其实json对应的函数和hstore的很像，基本格式一致，不同之处在于json里面嵌套现象比htore更普遍，
必要的时候需要递归调用才嫩解析出最里面那层的数值
xml
本来想说说xml在pg中的应用的，后来发现这个type已经有点土了，基本被大家抛弃中，还会不说了，有兴趣的同学可以参考一下这儿链接 http://www.postgresql.org/docs/9.3/static/datatype-xml.html
sql tricks
如何让in跑的更快
看log的时候经常能看见where id in (XXX,XXX,XXX)这样的格式，或者更高级一点的是写成 where id in (select id from table where condition)这种样子，其实吧，这种查询一旦数据量大了就会很慢，看个例子
qunar_group=# select count(1) from b2c_id_mapping ;
count
--------
859128
这个表有八百多万数据
qunar_group=# select array_agg(distinct 1) from b2c_id_mapping
where id in (select id from b2c_id_mapping limit 10000);
array_agg
-----------
{1}
(1 row)
Time: 333.813 ms
我弄了1w条来in，三百多ms，有点点慢
如果写成这样子呢
qunar_group=# select array_agg(distinct 1) from b2c_id_mapping
where id =any(array(select id from b2c_id_mapping limit 10000));
array_agg
-----------
{1}
(1 row)
Time: 28.174 ms
宾果，快了十多倍
这个例子的重点是善用数组的特性，any函数代表了搜索数组中任意匹配的元素
LIKE optimalization
很多时候，我们需要做前缀匹配的like，比如下面这样，但是木有索引的时候这种查询很慢
qunar_group=# \d b2c_id_mapping
Table "public.b2c_id_mapping"
Column | Type | Modifiers
--------+-----------------------+-----------
id | integer | not null
eid | character varying(32) | not null
Indexes:
"id_mapping_pk" PRIMARY KEY, btree (id)
"b2c_id_mapping_eid_idx" btree (eid)
qunar_group=# select count(1) from b2c_id_mapping where eid like '1245%';
count
-------
230
(1 row)
Time: 373.227 ms
然后我们搞一个神奇的前缀匹配索引
qunar_group=# CREATE INDEX like_index ON b2c_id_mapping(eid varchar_pattern_ops);
CREATE INDEX
try again
qunar_group=# select count(1) from b2c_id_mapping where eid like '1245%';
count
-------
230
(1 row)
Time: 2.992 ms
效果很神奇吧
前缀搞定了，那么后缀呢，后缀的效果如何
qunar_group=# select count(1) from b2c_id_mapping where eid like '%1245';
count
-------
83
(1 row)
Time: 264.719 ms
比较惨，我们再来个神奇的后缀索引
qunar_group=# CREATE INDEX rev_like_index ON b2c_id_mapping( (reverse(eid) ) varchar_pattern_ops);
CREATE INDEX
qunar_group=# select count(1) from b2c_id_mapping where reverse(eid) like reverse('%1245');
count
-------
83
(1 row)
Time: 1.205 ms
见证奇迹的时刻哦
Be carefull when using the NOT IN operator with NULL
pg的null是个神奇的存在，什么是null，null就是不知道，他和任何值做比较的结构都是不知道，所以他不等于任何值。如果null不幸出现在 not in这样的条件里，那么抱歉，输出的结果集必然是空。因为a not in (XXX,XXX) 要求左边的数据不等于右边列表中的任何值，一个null的出现导致结果永远是未可知。
postgres=# SELECT *
FROM (VALUES(10),(20),(30)) v(a)
WHERE a NOT IN (10, 20, NULL);
a
---
(0 rows) --> anomaly, expects 30
Intersection of arrays
如果我想求两个数组的交集，现有的数组函数没有直接的实现。不过可以转换思路吗，数组不能求交集表可以啊，unnest这时候就可以派上用场了
select array(select unnest(array['a','b','c'])
INTERSECT select unnest(array['a','b']));
array
-------
{b,a}
(1 row)
效果杠杠的
LIKE to list of patterns
like可以前后模糊匹配，这个很好，但是如果我想一次like好几个pattern呢，抱歉sql不支持
但是数组有个很好很强大的功能叫做any，前文也提到过any是找出数组中任意匹配的item，有了它我们可以把一个多pattern的like写成这个样子
qunar_group=# select 'pppabcdttt' ~~* any(array['%mab%','%cd%']);
?column?
----------
t
(1 row)
Time: 0.366 ms
qunar_group=# select 'pppabcdttt' ~~* any(array['%ab%','%tcd%']);
?column?
----------
t
(1 row)
Time: 0.322 ms
当然了，对pg熟悉的同学说你还可以写成这样嘛
select 'pppabcdttt' ~ '(ab|tcd)';
好吧，我承认这个更简单
Simply age calculation from birth date
如题，我想通过生日计算某个人多大年纪啦，还要按照国际通用惯例不过生日就不算长一岁那种算法，神马虚一岁虚两岁的算法统统表示不知道
pg有个age函数能够计算出指定时间点到当前时间的时间差
ly_test=# select age('2013-01-01'::date);
age
-----------------------
1 year 2 mons 12 days
(1 row)
结果是一串人类可读的文字，现在需要截取其中自己需要的部分
ly_test=# select extract(year from age('2013-01-01'::date));
date_part
-----------
1
(1 row)
直接把年截取出来了，这样就能很方便计算年龄了。这个函数用处挺多的，比如校验某些儿童是否可以买飞机的婴儿票
扫一扫
3236
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
