比如说,如果我有一个表,其中包含了每种设备类型和设备使用年限的设备ID,那么我怎么能计算出至少具有该设备使用年限的Count Distinct个设备ID.

例如,假设这是我们所有的数据:

equipment_type equipment_id equipment_age
Screwdriver A123 1
Screwdriver A234 2
Screwdriver A345 2
Screwdriver A456 2
Screwdriver A567 3

我希望输出为:

equipment_type equipment_age count_of_equipment_at_least_this_age
Screwdriver 1 5
Screwdriver 2 4
Screwdriver 3 1

原因是有5把螺丝刀至少使用了1天,4把螺丝刀至少使用了2天,只有1把螺丝刀至少使用了3天.

到目前为止,我只能计算每个设备使用年限内的设备数量(如下图所示),但不能计算"至少该设备使用年限".

SELECT
equipment_type,
equipment_age,
COUNT(DISTINCT equipment_id) as count_of_equipments
FROM equipment_table
GROUP BY 1, 2

推荐答案

考虑下面的少连接解决方案

select distinct
  equipment_type,
  equipment_age,
  count(*) over equipment_at_least_this_age as count_of_equipment_at_least_this_age
from equipment_table 
window equipment_at_least_this_age as (
  partition by equipment_type 
  order by equipment_age 
  range between current row and unbounded following
)     

如果应用于问题中的样本数据,则输出为

enter image description here

Sql相关问答推荐

有没有一种正确的方法来利用SQL UNION来从三个潜在查询中 Select 最大值?

在SQL中创建一个计数器,根据BigQuery/SQL中的条件递归地添加行值

Postgres:对包含数字的字符串列表进行排序

当交叉联接3个或更多表时,实体框架中是否会传输冗余的行数据并占用数据库带宽?

当一个视图在Postgres中失效时?

返回找到的最小和最大row_number()spark SQL

Pgsql-返回包含多行数据的行

从单个表达式中的分隔字符串中取平均值

SQL查询正在工作,但返回空结果

获取分布在同一行的列中的出现次数

将最近的结束日期与开始日期相匹配

如何 for each id创建长度等于id长度的不同日期序列?

两个具有 NULL 值的表达式结果之间的差异

用户定义的标量值函数是否仍然会阻止并行性?

每次计数器增加时通过运行总重置进行分组

SQL Server:时态表并在运行时添加计算列

使用row_number() over partition by保留首次出现且值不为空的行的方法

基于字符串的SQL查询

SQL 按 id 运行总计并受条件限制(在窗口上)

PostgreSQL Select 具有两列的自引用