问题

我有一个数据库，想要制作类似以下的东西：

获取类似这样的结果最简单的方法是什么？我有一个购买ID和购买金额的列表。我考虑使用CASE语句来处理处于这些金额范围内的购买，但ID的计数结果与预期不符。

答案1

得分: 1

以下是您提供的代码的翻译部分：

我们可以首先定义一个任意桶的数组，然后查找适合其中的行：

with buckets as (
    select value, lead(value) over(order by value) next_value
    from 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) c
from snowflake_sample_data.tpch_sf1.customer
join buckets
group by 1
order by any_value(value);

with buckets as (
	select value, lead(value) over(order by value) next_value
    from 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) c
from snowflake_sample_data.tpch_sf1.customer
join buckets
group by 1
order by any_value(value);

答案2

得分: 0

下面是代码的翻译部分：

一个相当简单的方法是使用CASE语句，规则按顺序评估，因此您只需要检查递增的切除值。

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;

给出的结果为：

<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>