我有一個表格，其中包含有關特定版本的每個用戶級別的資訊。假設它的名稱是 user_level_advanced。
| 版本 | 用戶身份 | 等級 |
|---|---|---|
| 0.9.3 | 1 | 2 |
| 0.9.5 | 2 | 3 |
| 0.9.3 | 3 | 4 |
| 0.9.3 | 4 | 5 |
我想計算每個級別高級的次數，以便每個用戶都應該考慮低于他們當前級別的級別。對于上表，結果應該是這樣的。
| 版本 | 等級 | 高級計數 |
|---|---|---|
| 0.9.3 | 1 | 3 |
| 0.9.3 | 2 | 3 |
| 0.9.3 | 3 | 2 |
| 0.9.3 | 4 | 2 |
| 0.9.3 | 5 | 1 |
| 0.9.5 | 1 | 1 |
| 0.9.5 | 2 | 1 |
| 0.9.5 | 3 | 1 |
with user_level_advanced as(
select
"0.9.3" as version, 1 as user_id, 2 as level_advanced_max
union all
select "0.9.5" as version, 2 as user_id, 3 as level_advanced_max
union all
select "0.9.3" as version, 3 as user_id, 4 as level_advanced_max
union all
select "0.9.3" as version, 4 as user_id, 5 as level_advanced_max
),
user_grouped_by as
(
select version, level_advanced_max, count(*) as level_advanced_count
from user_level_advanced
group by version, level_advanced_max
)
select version,
level_advanced_max,
sum(level_advanced_count) over(partition by version order by level_advanced_max asc rows between current row and unbounded following)
from user_grouped_by
我使用這個查詢來計算它，但它有一個缺陷，如果 user_level_advanced 表中缺少一個級別，那么結果表也將丟失它。謝謝你的幫助。
| 版本 | 等級 | 高級計數 |
|---|---|---|
| 0.9.3 | 2 | 3 |
| 0.9.3 | 4 | 2 |
| 0.9.3 | 5 | 1 |
| 0.9.5 | 3 | 1 |
uj5u.com熱心網友回復：
考慮以下方法
select version, level, count(*) advanced_count
from user_level_advanced,
unnest(generate_array(1, level_advanced_max)) level
group by version, level
如果應用于您問題中的樣本資料 - 輸出是
uj5u.com熱心網友回復：
您可以使用cross join生成所有可能的（級別、版本）對，然后將其加入您的表以獲得預期結果：
with recursive levels as
(select 1 as level
union all
select level 1 from levels
where level < (select max(level) from user_level_advanced)),
versions as
(select distinct version from user_level_advanced),
cte as
(select * from versions cross join levels)
select cte.version, cte.level, count(*)
from cte inner join user_level_advanced ula
on ula.level >= cte.level and ula.version = cte.version
group by cte.version, cte.level
小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/qianduan/430062.html