我有幾個表（pega.race 也存在，我們只是不需要任何資料）
CREATE TABLE pega.pega_race (
id bigserial NOT NULL,
race_id int8 NOT NULL,
pega_id int8 NOT NULL,
"result" int4 NOT NULL,
ts timestamp NULL DEFAULT now_utc(),
CONSTRAINT pega_race_pk PRIMARY KEY (id)
)
CREATE TABLE pega.pega (
id int8 NOT NULL,
speed numeric NULL,
strength numeric NULL,
lightning numeric NULL,
wind numeric NULL,
water numeric NULL,
fire numeric NULL,
CONSTRAINT pega_pk PRIMARY KEY (id)
);
我想要的是獲得一個范圍內速度/強度/風/閃電/水/火的每個組合（排列？）的平均結果。所以從 0-2.25、2.25-4.5、4.5-6.75 和 6.75-9。它們必須在 0-9 之間。在此之外不存在任何價值。
所以我想要一個 6.75-9 速度、2.25-4.5 強度、4.5-6.75 風、2.25-4.5 閃電、0-2.25 水、0-2.25 火以及所有其他組合的平均結果。我沒有足夠的資料來簡單地像這樣四舍五入
SELECT round(speed) speed, round(pega.strength) strength, round(pega.lightning) lightning, round(pega.wind) wind, round(pega.water) water, round(pega.fire) fire,
avg(result),
count(*)
FROM pega.pega
JOIN pega.pega_race pr ON pega.id=pr.pega_id
GROUP BY 1, 2, 3, 4, 5, 6
HAVING count(*) > 20
ORDER BY avg(result)
所以我想擴大這些范圍，將可能性的數量減少幾個因素。
查看與此類似的其他帖子，我找到了在單個列上按范圍分組的解決方案，我正在努力將其擴展到多個列。
這是我所做的嘗試
with series as (
SELECT generate_series(0, 9-2.25, 2.25) as r_from
), range as (
SELECT r_from, r_from 2.25 as r_to FROM series
), pega_data as (
SELECT speed, strength, lightning, wind, water, pega.fire, result
FROM pega.pega
JOIN pega.pega_race pr ON pega.id=pr.pega_id
JOIN pega.race ON pr.race_id=race.id
)
SELECT r_from, r_to,
(SELECT count(*) speed_count FROM pega_data WHERE speed between r_from and r_to),
(SELECT count(*) strength_count FROM pega_data WHERE strength between r_from and r_to),
(SELECT count(*) lightning_count FROM pega_data WHERE lightning between r_from and r_to),
(SELECT count(*) wind_count FROM pega_data WHERE wind between r_from and r_to),
(SELECT count(*) water_count FROM pega_data WHERE water between r_from and r_to),
(SELECT count(*) fire_count FROM pega_data WHERE fire between r_from and r_to),
(SELECT AVG(result) FROM pega_data WHERE speed between r_from and r_to AND strength between r_from and r_to AND lightning between r_from and r_to AND wind between r_from and r_to AND water between r_from and r_to AND fire between r_from and r_to)
FROM range
我想我快到了？但不確定如何將所有內容分解為每個組合。
謝謝。
此外，如果你能帶領我獲得第一名的百分比，則可以加分。（結果 == 1）。如果您可以引導我走一條更具統計性的路線，該路線可以拉出眾多列之間的相關性和較低的結果/較高的 1st %，則額外的額外獎勵積分。這個我什至不知道如何開始..最后（也是唯一的）統計課程是 5 年前的現在..
uj5u.com熱心網友回復：
由于常規資料結構，您無需顯式生成系列并檢查范圍。嘗試使用算術運算按范圍索引 0..3 獲取統計資訊。例如
select floor(speed/2.25)::int r_speed, floor(strength/2.25)::int r_strength, floor(lightning/2.25)::int r_lightning, floor(wind/2.25)::int r_wind, count(*) n
from pega_data
group by r_speed, r_strength, r_lightning, r_wind
order by r_speed, r_strength, r_lightning, r_wind
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/454915.html
標籤：sql PostgreSQL 通过...分组