首届NoCOUG国际SQL挑战赛

〇〇 · 发表于 2025-5-13 10:11

postgresql也不支持聚合函数，但分析函数性能比oracle好很多

postgres=# WITH recursive t(v,p,lvl,rn) AS (
SELECT face_value v
,probability p
,1
,1
FROM die
UNION ALL
SELECT d.face_value + t.v ---- 第N次投掷，面值为前N-1次的各种结果加上die表的六种可能面值
,SUM(d.probability * t.p)over(partition BY d.face_value + t.v)::decimal(400,390) ---- 把相同的总面值的概率加起来
,(t.lvl)+1 ,row_number()over(partition BY d.face_value + t.v order by 1) ::int
FROM die d,t
WHERE t.lvl<100 and t.rn=1
---- 按照总面值分组聚合，再做下一次递归
)
select count(*),max(p)::float,min(p)::float from(
SELECT v,sum(p)p
FROM t
WHERE lvl = 100 and rn=1
group by v)
;
count | max | min
-------+----------------------+-------------------------
699 | 0.019748498208970528 | 1.2074673472413666e-108
(1 row)
Time: 671.816 ms
oracle 19c要26秒

复制代码

〇〇 · 发表于 2025-5-14 09:41

postgresql把union all改成union就达到去重复目的，不需要用rn标记了,数据量少不明显，大比rn快

postgres=# WITH recursive t(v,p,lvl) AS (
SELECT face_value v
,probability p
,1
FROM die
UNION
SELECT d.face_value + t.v ---- 第N次投掷，面值为前N-1次的各种结果加上die表的六种可能面值
,SUM(d.probability * t.p)over(partition BY d.face_value + t.v)::decimal(400,390) ---- 把相同的总面值的概率加起来
,(t.lvl)+1
FROM die d,t
WHERE t.lvl<100
---- 按照总面值分组聚合，再做下一次递归
)
select count(*),max(p)::float,min(p)::float from(
SELECT v,sum(p)p
FROM t
WHERE lvl = 100
group by v)
;
count | max | min
-------+----------------------+-------------------------
699 | 0.019748498208970528 | 1.2074673472413666e-108
(1 row)
Time: 1163.180 ms (00:01.163)

复制代码