PostgreSQL Notes

PostgreSQL是非常优秀的开源关系型数据库,提供各种好用的函数,有人说它是全栈型数据库,但显然会有人怀疑它在各个方面表现出的专业性,更多请自行Google了,或者访问PostgreSQL 在线文档 。这里只是记录一些工作中我认为有意思的用法。

1、针对某列中的多个数据进行分组统计,其中列为逗号分隔的数据,比如a,b,c

--Unfriendly
SELECT
  gid
  , count(1) num
FROM (
  SELECT gid
  FROM regexp_split_to_table((SELECT array_to_string(array(SELECT gid FROM group), ',')), ',') gid) tt
GROUP BY gid;

--Friendly
SELECT
  gid
  , count(1) num
FROM (
  SELECT gid
  FROM unnest(string_to_array(array_to_string(array(SELECT gid  FROM group), ','), ',')) gid) tt
GROUP BY gid;

思路:把列转换为一个数组(一行数据) -> 把数组转换为以逗号分隔(,)的字符串 -> 再将字符串转换数组 -> 数组转换为列。

以上两种方式均可实现,但性能天差万别(只看执行计划没有多大差别),区别在于regexp_split_to_table和unnest两个函数。

demo :

gid(string) -> gid(array) -> gid(string)
a,b,c {a,b,c,b,d,a,c} a
b,d b
a,c c
b
d
a
c

 

2、针对PostGIS查询最短距离:

SELECT
  geo.x
  , geo.y
FROM geo_table AS geo
ORDER BY geo.geom <-> 'SRID=4326;POINT(114.423842 24.465537)'::GEOMETRY
LIMIT 1;
Leave a Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: