sql - 分组依据以及子句外部的列

Question

我有一个SQL表如下

    id    |date_accessed     
----------+------------
     1    | 16/10/2014
     1    | 28/10/2014
     1    | 25/11/2014
     1    | 16/12/2014
     2    | 30/09/2014
     2    | 03/10/2014
     2    | 17/10/2014
     2    | 03/01/2015

我需要按月和年对数据进行分组，但我还想知道自用户第一次访问系统以来有多少个月

    id    |   month    |   year     |   length_in_month
----------+------------+------------+-------------------
     1    |    10      |   2014     |          1
     1    |    11      |   2014     |          2     
     1    |    12      |   2014     |          3
     2    |    09      |   2014     |          1
     2    |    10      |   2014     |          2
     2    |    01      |   2015     |          5

我的查询如下

select 
    id, 
    Extract(MONTH from "date_accessed") as month, 
    Extract(year from "date_accessed") as year 
from 
    exampleTable 
group by 
    1, 2, 3 
order by 
    1, 3, 2 

但是当我进行分组时，我无权访问 min(date_accessed)，以获取 length_in_month 列的长度。

有解决办法吗？

Answer 1

我已经使用 AGE 函数来确定首次访问月份的开始日期和实际访问日期的结束日期之间的差异，以给出正如您提到的，一个可以被视为一个月的间隔，然后将其加 1。这给出了预期的结果。

first_access 在 CTE 中单独计算，因为它是每个 ID 的单个值，而不是每个 ID、月、年。

with m AS
(
select id, min(date_accessed)
                    as first_access from t
group by id
)
select t.id, Extract(MONTH from "date_accessed") as month, 
             Extract(year from  "date_accessed") as year,
            EXTRACT ( month from 
                      MIN( AGE( date_trunc('month', date_accessed) 
                                + interval '1 month - 1 day',  --last day of month
                             date_trunc('month', first_access) --first day of month
                         ))
                    ) + 1 as length_in_month
from t join m on t.id = m.id 
group by t.id,month,year 
order by 1,3,2;

DEMO