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_groupFROM 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