英文:
Snowflake Grouping by Numbers
问题
我有一个数据库，想要制作类似以下的东西：
| 金额 | 数量 |
|---|---|
| $ 0 - $500 | 3 |
| $501 - $1999 | 10 |
| $2000 - $2999 | 23 |
获取类似这样的结果最简单的方法是什么？我有一个购买ID和购买金额的列表。我考虑使用CASE语句来处理处于这些金额范围内的购买，但ID的计数结果与预期不符。
英文:
I have a database and I am wanting to produce something similar to the below:
| Dollar Amount | Count |
|---|---|
| $ 0 - $500 | 3 |
| $501 - $1999 | 10 |
| $2000 - $2999 | 23 |
What is the easiest way to get something similar to that? I have a list of purchase IDs and dollar amount of purchase. My thought was using cases when purchase between those amounts but the count of IDs isn't working as expected.
答案1
得分: 1
以下是您提供的代码的翻译部分：
我们可以首先定义一个任意桶的数组，然后查找适合其中的行：
with buckets as (select value, lead(value) over(order by value) next_valuefrom table(flatten([0,500,1000,2000]))qualify next_value is not null)select value||'-'||next_value range, count_if(c_acctbal>=value and c_acctbal<next_value) cfrom snowflake_sample_data.tpch_sf1.customerjoin bucketsgroup by 1order by any_value(value);
英文:
We can start by defining an array of arbitrary buckets, and then looking for rows that fit within:
with buckets as (select value, lead(value) over(order by value) next_valuefrom table(flatten([0,500,1000,2000]))qualify next_value is not null)select value||'-'||next_value range, count_if(c_acctbal>=value and c_acctbal<next_value) cfrom snowflake_sample_data.tpch_sf1.customerjoin bucketsgroup by 1order by any_value(value);
答案2
得分: 0
下面是代码的翻译部分：
一个相当简单的方法是使用CASE语句，规则按顺序评估，因此您只需要检查递增的切除值。with data as (select * from values(1),(2),(499),(501),(1000),(1499),(2014))selectcasewhen column1 <= 500 then '$ 0 - $500'when column1 <= 1999 then '$501 - $1999'when column1 <= 2999 then '$2000 - $2999'end as "Dollar Amount",count(*) as "Count"from datagroup by 1;
给出的结果为：
| Dollar Amount | Count |
|---|---|
| $ 0 - $500 | 3 |
| $501 - $1999 | 3 |
| $2000 - $2999 | 1 |
<details><summary>英文:</summary>A rather simple method is to use a CASE statement, the rules are evaluated in order, so you just need to check increasing cutout values.
with data as (
select * from values
(1),(2),(499),
(501),(1000),(1499),
(2014)
)
select
case
when column1 <= 500 then '$ 0 - $500'
when column1 <= 1999 then '$501 - $1999'
when column1 <= 2999 then '$$2000 - $2999'
end as "Dollar Amount"
,count(*) as "Count"
from data
group by 1;
gives:Dollar Amount |Count--|--|--$ 0 - $500 |3$501 - $1999 |3$$2000 - $2999 |1</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论