union和where语句的替代方法是按列对bucket age和group进行sql impala

eimct9ow 于 2021-06-26 发布在 Impala

关注(0)|答案(1)|浏览(351)

给你一张这样的table：

+----+-----------+------------------+
| id | code      | age              |
+----+-----------+------------------+
| 1  | 315.32000 | 2.18430371791803 |
| 1  | 315.32000 | 3.18430371791803 |
| 1  | 800.00000 | 2.18430371791803 |
| 2  | 315.32000 | 5.64822705794013 |
| 3  | 800.00000 | 5.68655778752176 |
| 3  | 120.12000 | 5.70572315231258 |
| 4  | 315.32000 | 5.72488851710339 |
| 4  | 315.32000 | 5.74405388189421 |
| 5  | 120.12000 | 5.7604813374292  |
| 6  | 315.32000 | 5.77993740687426 |
| .. | ...       | ...              |
+----+-----------+------------------+

我正在使用：

SELECT code, COUNT(*) AS code_frequency,'0-10' AS age_range
FROM table 
WHERE age >= 0 AND age < 10
GROUP BY code 
ORDER BY code_frequency DESC LIMIT 1
UNION
SELECT code, COUNT(*) AS code_frequency,'10-20' AS age_range
FROM table
WHERE age >= 10 AND age < 20
GROUP BY code 
ORDER BY code_frequency DESC LIMIT 1
UNION
...
ORDER BY age_range

我用稍微不同的年龄范围和逻辑重复了多次，输出一个表，显示每个年龄组中频率最高的最频繁代码及其频率。
输出：

+-----------+-----------+-----------+
| code      | frequency | age_range |
+-----------+-----------+-----------+
| 315.32000 | 99832     | 0-10      |
| 800.00000 | 45223     | 10-20     |
| ...       | ...       | ...       |
+-----------+-----------+-----------+

有没有更有效的方法来实现相同的输出，而不必重复相同的代码块和使用并集？
干杯

sql impala group-by pivot Bucket

来源：https://stackoverflow.com/questions/60774574/alternative-to-union-and-where-statement-to-bucket-age-and-group-by-a-column-sql

1条答案

按热度按时间

ldioqlga1#

可以将聚合与 case 用于定义组的表达式：

select
    code,
    count(*) frequency,
    case 
        when age >= 0  and age < 10 then '0-10'
        when age >= 10 and age < 20 then '10-20'
    end age_range
from mytable
group by code, age_range
order by age_range

如果您希望每个年龄组使用最频繁的代码，那么可以使用 row_number() 在查询顶部：

select code, frequence, age_range
from (
    select 
        t.*, 
        row_number() over(partition by age_range order by frequency desc) rn
    from (
        select
            code,
            count(*) frequency,
            case 
                when age >= 0  and age < 10 then '0-10'
                when age >= 10 and age < 20 then '10-20'
            end age_range
        from mytable
        group by code, age_range
    ) t
) t
where rn = 1

或者更好：

select code, frequence, age_range
from (
    select
        code,
        count(*) frequency,
        case 
            when age >= 0  and age < 10 then '0-10'
            when age >= 10 and age < 20 then '10-20'
        end age_range,
        row_number() over(partition by min(age) order by count(*) desc) rn
    from mytable
    group by code, age_range
) t
where rn = 1

赞(0）回复(0）举报 2021-06-26

我来回答

union和where语句的替代方法是按列对bucket age和group进行sql impala

1条答案

相关问题

热门标签

最新问答