psql 按值平均

需求

学生成绩表

• 创建表初始化

CREATE TABLE test(
  name VARCHAR(20),
  score FLOAT
);
INSERT INTO test(name, score) VALUES('A', 1);
INSERT INTO test(name, score) VALUES('B', 3);
INSERT INTO test(name, score) VALUES('C', 6);
INSERT INTO test(name, score) VALUES('D', 10);
INSERT INTO test(name, score) VALUES('E', 13);
INSERT INTO test(name, score) VALUES('F', 14);
INSERT INTO test(name, score) VALUES('F2', 14);
INSERT INTO test(name, score) VALUES('G', 18);
INSERT INTO test(name, score) VALUES('H', 20);
INSERT INTO test(name, score) VALUES('H2', 20);

按照成绩值区间分成4份

WITH test_avg(max_test, min_test) AS (
  SELECT MAX(score) + 0.00001,
    MIN(score)
  FROM public.test
), test_range AS (
  SELECT ((SELECT min_test FROM test_avg) + ((SELECT max_test FROM test_avg) - (SELECT min_test FROM test_avg)) * (i-1) / 4) AS left_range,
   ((SELECT min_test FROM test_avg) + ((SELECT max_test FROM test_avg) - (SELECT min_test FROM test_avg)) * i / 4) AS right_range
  FROM GENERATE_SERIES(1, 4) as i
), test_avg_data AS (
  SELECT a.left_range, a.right_range, AVG(score) AS score_avg FROM public.test AS t
    RIGHT JOIN test_range AS a
  ON score BETWEEN a.left_range AND a.right_range
  GROUP BY a.left_range, a.right_range
)
SELECT * FROM test_avg_data;

• 重点在于

 SELECT ((SELECT min_test FROM test_avg) + ((SELECT max_test FROM test_avg) - (SELECT min_test FROM test_avg)) * (i-1) / 4) AS left_range,
   ((SELECT min_test FROM test_avg) + ((SELECT max_test FROM test_avg) - (SELECT min_test FROM test_avg)) * i / 4) AS right_range
  FROM GENERATE_SERIES(1, 4) as i

这里通过 GENERATE_SERIES(1, 4) 实现了类似一个 for 循环，用 SQL 实现了遍历
结果如图：

按照人数分组

SELECT
  name,
  score,
  NTILE(4)
  OVER (
    ORDER BY score ) AS name_group
FROM public.test;

这种方式比较简单，可以用窗口函数 NTILE 函数解决
结果如图：

方法一：
insert into  shorturl.urls(code,org_url) values('cpt','baidu.com2') ON CONFLICT (code) DO UPDATE set org_url=excluded.org_url;
如果有主键冲突，可以这么去更新，和mysql on  on duplicate update 一样。
方法二：
CREATE RULE fund_basic_info AS ON INSERT TO fund.fund_basic_info WHERE EXISTS (SELECT 1 FROM fund.fund_basic_info WHERE fund.fund_basic_info.fund_code = new.fund_code) DO INSTEAD NOTHING;

本文感谢好友@Kevin