时间戳

首页 > 解决方案 > 具有具有最小值、最大值的 SQL 语句

sql - 具有具有最小值、最大值的 SQL 语句

问题描述

我有一张桌子：

 ID                INTEGER NOT NULL,  -- AUTOMATIC RECORD'S ID
 CUSTOMER_ID       INTEGER NOT NULL,
 BILING_PERIOD     DATE    NOT NULL,
 DOCUMENT_ID       INTEGER NOT NULL,   
 DATE_CREATED      DATE    NOT NULL  -- WHEN THE DOCUMENT WAS CREATED

我想为客户在计费期间选择文档数量，为客户在计费期间首先创建的文档的 ID 和在客户计费期间最后创建的文档的 ID。所有应按客户和计费周期分类。我只想要为客户提供超过 1 个文档的计费周期。

因此，当我们有例如这样的数据时：

ID CUSTOMER_ID BILING_PERIOD DOCUMENT_ID DATE_CREATED
1  5           2020-01-01    123         2020-02-01
2  5           2020-01-01    22          2019-02-01
3  5           2020-01-01    3           2010-02-01
4  99          2020-01-01    458         2021-02-01
5  99          2020-01-01    64          2010-02-01
6  100         2020-01-01    120         2020-02-01
7  99          2019-06-01    452         2019-06-01
8  99          2019-06-01    546         2019-12-01

我希望我的结果如下所示：

CUSTOMER_ID BILING_PERIOD NR_OF_DOC FIRST_DOC_ID LAST_DOC_ID
5           2020-01-01    3         3            123
99          2019-06-01    2         452          546
99          2020-01-01    2         64           458

我自己我只能计算每个用户和期间的文档数量

SELECT customer_id, biling_period, count(*) as nr_of_doc
FROM T1
GROUP BY customer_id, biling_period
HAVING COUNT() > 1;

CUSTOMER_ID BILING_PERIOD NR_OF_DOC 
5           2020-01-01    3         
99          2019-06-01    2         
99          2020-01-01    2

我不知道如何获取最新和最旧文档的 document_id。

标签： sqlgroup-bycountpivotwindow-functions

解决方案

您可以使用row_number()和聚合：

select 
    customer_id,
    billing_period,
    count(*),
    max(case when rn_asc  = 1 then document_id end) fist_doc_id,
    max(case when rn_desc = 1 then document_id end) last_doc_id
from (
    select 
        t.*,
        row_number() over( 
            partition by customer_id, billing_period order by date_created
        ) rn_asc,
        row_number() over( 
            partition by customer_id, billing_period order by date_created desc
        ) rn_desc
    from t1 t
) t
group by customer_id, billing_period
having count(*) > 1
order by customer_id, billing_period

即使文档 ID 不是按顺序排列的，这也会正常工作。

DB Fiddle 上的演示：

客户 ID | 计费周期 | 计数 | 拳头文档ID | last_doc_id
----------: | :------------- | ----: | ----------: | ----------:
          5 | 2020-01-01 | 3 | 3 | 123
         99 | 2019-06-01 | 2 | 第452章 546
         99 | 2020-01-01 | 2 | 64 | 458

推荐阅读