Блог

Главная - Вопросы по программированию - Группировать по наряду со столбцами, внешними по отношению к предложению

Группировать по наряду со столбцами, внешними по отношению к предложению

#sql #postgresql #group-by

#sql #postgresql #группировать по

Вопрос:

У меня есть таблица 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) , когда я делаю group by, чтобы получить длину length_in_month столбца.

Есть ли решение для этого?

Комментарии:

1. На каком основании вы выбрали интервал в один месяц, когда он должен быть равен нулю месяцев? не должно ли быть 0 месяцев для 10/2014 , поскольку к нему был получен первый доступ в том же месяце — 16/10/2014 ?

2. В идеале, это представляет собой проверку активности клиентов в конце каждого месяца. Итак, для этого процесса он начинается как минимум с 1. Но это бизнес-требование, если я могу сгенерировать 0, добавление 1 к нему является простым процессом.

3. почему в результатах два октября 2014 года?

4. Дата уникальна для каждого идентификатора

Ответ №1:

Я использовал AGE функцию для определения разницы между датой начала месяца первого обращения и датой окончания фактической даты обращения, чтобы задать интервал, который можно было бы справедливо рассматривать как месяц, а затем добавить к нему 1, как вы упомянули. Это дает ожидаемый результат.

first_access вычисляется отдельно в CTE, поскольку это единственное значение для каждого идентификатора, а не для каждого идентификатора, месяца, года.

 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;

ДЕМОНСТРАЦИЯ

Ответ №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

Ответ №3:

Я думаю, что сначала вам нужно выбрать Id и сгруппировать min (месяц) по Id, чтобы вы получили первую дату для каждого Id. Затем еще один выбор, подобный тому, который вы сделали, плюс выбор, который я предлагаю выше.

Ответ №4:

Приведенный ниже запрос дает вам точную продолжительность в месяцах. Учитывая приведенный выше пример ввода, запрос выдаст вам длительность length_in_months равной 0, если разница во времени составляет менее 30 дней. Умножение на -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

Ответ №5:

Другой подход

Оперативный тест: 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 |

Ответ №6:

Используйте следующее

Оперативный тест: 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 |

Ответ №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;

Вывод:

введите описание изображения здесь