按照外部列进行分组

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 

但是当我使用group by时，我无法访问min(date_accessed)以获取length_in_month列的长度。是否有解决方案？

- Visahan

---

你选择间隔为一个月的依据是什么？当它应该是零个月时，为什么不是零个月呢？因为10/2014在同一个月内首次访问 - 16/10/2014。 - Kaushik Nayak

理想情况下，这代表每个月末检查客户活动。因此，对于这个过程，它从至少1开始。但这是业务要求，如果我可以生成0，将其加1就是一个简单的过程。 - Visahan

为什么结果中有两个2014年10月？ - Michael Buen

日期对于每个ID是唯一的。 - Visahan

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

- Kaushik Nayak

Answer 2

使用子查询，如下所示：

SELECT 
    exampleTable.id, 
    EXTRACT(month FROM "date_accessed") AS month, 
    EXTRACT(year FROM "date_accessed") AS year,
    /* Calculate # months since the user accessed the system for the 1st time */
    (EXTRACT(year from "date_accessed") - EXTRACT(year from firstTimeAccessDatesTable.firstAccessDate)) * 12
    + (EXTRACT(month from "date_accessed") - EXTRACT(month from firstTimeAccessDatesTable.firstAccessDate)) + 1 AS length_in_month
FROM 
    /* Join exampleTable with firstTimeAccessDatesTable by id */
    exampleTable
INNER JOIN(
    /* Perform subquery to obtain the date a given user accessed the system for the first time */
    SELECT
        id,
        MIN("date_accessed") AS firstAccessDate
    FROM
        exampleTable
    GROUP BY
        1
    ) AS firstTimeAccessDatesTable
ON exampleTable.id = firstTimeAccessDatesTable.id
GROUP BY
    1, 2, 3, 4
ORDER BY
    1, 3, 2

- HumbleOne

Answer 3

我认为你需要先按Id选择Id和min(month)进行分组，这样你就可以得到每个Id的第一个日期。 然后进行另一个选择，类似于你所做的选择再加上我上面建议的选择。

Translated text:

我认为你需要先按Id选择Id和min(month)进行分组，这样你就可以得到每个Id的第一个日期。 然后进行另一个选择，类似于你所做的选择再加上我上面建议的选择。

- J.K

Answer 4

以下查询可以精确计算月份。以您上面的示例输入为参考，如果时间差小于30天，则查询将给出长度为0的length_in_months持续时间。乘以-1是为了将负持续时间转换为正值显示。

create table Test(id integer, date_accessed date);
insert into Test values(1, "2014-10-16");
insert into Test values(1, "2014-10-28");
insert into Test values(1, "2014-11-25");
insert into Test values(1, "2014-12-16");
insert into Test values(2, "2014-09-30");
insert into Test values(2, "2014-10-03");
insert into Test values(2, "2014-10-17");
insert into Test values(2, "2015-10-16");


select a.id, a.month, a.year, a.date_accessed, (timestampdiff(MONTH, 
a.date_accessed, a.min_date)) * -1 as length_in_month from (
select id, EXTRACT(MONTH FROM date_accessed) as MONTH, EXTRACT(YEAR FROM 
date_accessed) as YEAR, date_accessed, (select MIN(date_accessed) from Test) as 
min_date from Test order by date_accessed) a order by a.id asc;

Output
1   10  2014    2014-10-16  0
1   10  2014    2014-10-28  0
1   11  2014    2014-11-25  1
1   12  2014    2014-12-16  2
2   9   2014    2014-09-30  0
2   10  2014    2014-10-03  0
2   10  2014    2014-10-17  0
2   10  2015    2015-10-16  12

- khalid jahangeer

Answer 5

请使用以下内容：

在线测试：http://sqlfiddle.com/#!17/7c833/6

-- drop table t;

/*
create table t as
select id, date_accessed::date
from (values
     (1, '2014-10-16'),
     (1,  '2014-10-28'),
     (1,  '2014-11-25'),
     (1,  '2014-12-16'),
     (2,  '2014-09-30'),
     (2, '2014-10-03'),
     (2, '2014-10-17'),
     (2, '2015-01-03')
) as x(id, date_accessed)
*/

with unique_months as
(
    select 
        id,

        date_trunc('month', date_accessed) as monthify
    from t 
    group by id, monthify

)
, compute_length as
(                   
    select 
        id, monthify,

        ( 
            ( 
                extract(year from monthify) - extract(year from min(monthify) over(partition by id)) 
            ) * 12 
        )
        +
        ( 
            extract(month from monthify) - extract(month from min(monthify) over(partition by id))
        )
        +
        1 as length_in_month


    from unique_months
)
select id, 
  extract(year from monthify) "year", 
  extract(month from monthify) "month",
  length_in_month
from compute_length
order by id, monthify

结果：

| id | year | month | length_in_month |
|----|------|-------|-----------------|
|  1 | 2014 |    10 |               1 |
|  1 | 2014 |    11 |               2 |
|  1 | 2014 |    12 |               3 |
|  2 | 2014 |     9 |               1 |
|  2 | 2014 |    10 |               2 |
|  2 | 2015 |     1 |               5 |

- Michael Buen

Answer 6

另一种方法

实时测试：http://sqlfiddle.com/#!17/7c833/2

-- drop table t;

/*
create table t as
select id, date_accessed::date
from (values
     (1, '2014-10-16'),
     (1,  '2014-10-28'),
     (1,  '2014-11-25'),
     (1,  '2014-12-16'),
     (2,  '2014-09-30'),
     (2, '2014-10-03'),
     (2, '2014-10-17'),
     (2, '2015-01-03')
) as x(id, date_accessed)
*/

with unique_months as
(
    select 
        id, 
        extract(year from date_accessed) "year",
        extract(month from date_accessed) "month",
        min(date_accessed) as month_representative
    from t 
    group by id, year, month

)
, compute_length as
(                   
    select 
        id, year, month,

        ( 
            ( 
                extract(year from month_representative) - extract(year from min(month_representative) over(partition by id)) 
            ) * 12 
        )
        +
        ( 
            extract(month from month_representative) - extract(month from min(month_representative) over(partition by id))
        )
        +
        1 as length_in_month


    from unique_months
)
select * 
from compute_length
order by id, year, month

结果：

| id | year | month | length_in_month |
|----|------|-------|-----------------|
|  1 | 2014 |    10 |               1 |
|  1 | 2014 |    11 |               2 |
|  1 | 2014 |    12 |               3 |
|  2 | 2014 |     9 |               1 |
|  2 | 2014 |    10 |               2 |
|  2 | 2015 |     1 |               5 |

- Michael Buen

Answer 7

如果Postgres具有内置的DATEDIFF，则查询最短。

使用DISTINCT ON可以使查询更符合惯用法。

DISTINCT ON是特定于Postgres的。它丢弃重复行并仅保留一行，并根据传递给它的参数对行进行排序。

-- http://www.sqlines.com/postgresql/how-to/datediff
create or replace function month_diff (start_month date, end_month date) 
returns int as $$
begin
    return (date_part('year', end_month) - date_part('year', start_month))*12 +
            date_part('month', end_month) - date_part('month', start_month);
end;
$$ language 'plpgsql' immutable;


select
    distinct on (id, date_trunc('month', date_accessed))

    id, 
    date_part('year', date_accessed) as year,
    date_part('month', date_accessed) as month,

    month_diff( min(date_accessed) over(partition by id), date_accessed ) + 1 
        as length_in_month
from t;

输出：

- Michael Buen