Как вытащить значения для каждого начала и конца года списка годов?

Question

[Семён Семёнов]

Есть таблица, которая хранит дату и число:
| id | t | item_code |
---------------------------------------------
| 1 | 2008-02-04 | 11 |
| 2 | 2008-04-04 | 12 |
| 3 | 2008-06-04 | 13 |
| 4 | 2008-09-04 | 14 |
| 5 | 2009-02-04 | 21 |
| 6 | 2009-04-04 | 22 |
| 7 | 2009-06-04 | 23 |
| 8 | 2009-09-04 | 24 |
| 9 | 2010-02-04 | 31 |
|10 | 2010-04-04 | 32 |
|11 | 2010-06-04 | 33 |
|12 | 2010-09-04 | 34 |

Я хотел бы получить на выходе:

| 2008-02-04 | 2008-09-04 | 11 | 14 |
| 2009-02-04 | 2009-09-04 | 21 | 24 |
| 2010-02-04 | 2010-09-04 | 31 | 34 |

Т.е. получается что мне в каждой строке нужны минимальная и максимальная дата каждого года + значения для каждой из этих дат.

Такое вообще возможно сделать не прибегая к курсорам и всему подобному? Хотелось бы обычным запросом решить. Но меня смущает цикл. Он ведь здесь нужен. Ну или объединять в кучу union.

Попробовал так:

SELECT MIN(t::timestamp::date) AS startyear, MAX(t::timestamp::date) as endyear, item_code
FROM item_codes
GROUP BY item_code
order by startyear

... но вышла какая-то фигня.

Answer 1

[Артур Полозов]

Сгруппировать по году из даты.

Group by date_trunc('year', ДАТА)

Comments

[Семён Семёнов]

Да, но тогда не увижу item_code для каждого из годов. Т.е. будут только даты, но не даты+их значения.

[Артур Полозов]

вот пример на MS SQL
думаю на постгресе аналогично, немного синтаксис подправить:

SELECT year_D, min_D, max_D, MIN(s.maincode), MIN(s2.maincode)
FROM (
select datepart(year,edit_dat) as year_D, min(edit_dat) as min_D, max(edit_dat) as max_D
from spr
group by datepart(year,edit_dat)
) G
LEFT JOIN spr s ON s.edit_dat = G.min_D
LEFT JOIN spr s2 ON s2.edit_dat = G.max_D
GROUP BY year_D, min_D, max_D

Вторая группировка только для того, что в одну мин или макс дату может быть несколько кодов или что там..

[Семён Семёнов]

Артур Полозов: Любопытно... Спасибо, попробую разобраться и отпишусь.

[Семён Семёнов]

Артур Полозов: Не совсем так. Вы посчитали, что item_code (в вашем случае maincode) тоже должны быть мин и макс, хотя там нужно просто их значения за дату. Т.е. для 2008-02-04 это 11, а для 2008-09-04 это 14. Не минимальное значение и максимальное, а просто значнеия.

[Артур Полозов]

Семён Семёнов:
вам лучше знать может ли за дату 2008-02-04 быть два значения item_code ?!
еслу будет два.. то без доп группировки получится что то типа этого:
year = 2008 / start = 2008-02-04 / end = 2008-09-04 / item_s = 11 / item_e = 14
year = 2008 / start = 2008-02-04 / end = 2008-09-04 / item_s = 12 / item_e = 14

[Артур Полозов]

для вашего примера вторая группировка не нужна)

[Семён Семёнов]

Артур Полозов: Спасибо вам за помощь, но пример ниже работает несколько быстрее. Но правда спасибо, что откликнулись.

Answer 2

[Андрей Мохов]

как-то так:

WITH item_codes_ordered as (
        SELECT ic.*,
            COUNT(*) OVER (PARTITION BY EXTRACT(YEAR FROM ic.t) ORDER BY ic.id ASC) as a,
            COUNT(*) OVER (PARTITION BY EXTRACT(YEAR FROM ic.t) ORDER BY ic.id DESC) as d
        FROM item_codes ic
    )
SELECT ic_a.t as t_asc,
    ic_a.item_code as item_code_asc,
    ic_d.t as t_desc,
    ic_d.item_code as item_code_desc
FROM item_codes_ordered ic_a
INNER JOIN item_codes_ordered ic_d ON ic_d.d = 1 AND EXTRACT(YEAR FROM ic_d.t) = EXTRACT(YEAR FROM ic_a.t)
WHERE ic_a.a = 1

Comments

[Семён Семёнов]

Поразительно. Работает быстро и то, что нужно. Вас не затруднит чуть рассказать о запросе, чтобы не занимался тупым копированием? В частности, не понимаю, зачем вы первой выборке в PARTITION сортировали данные в разные стороны.

P.S. Что такое PARTITION вот только сейчас читаю. Никогда ранее не слышал и не работал с ним.

[Андрей Мохов]

Семён Семёнов: OVER это оконные функции...
если говорить совсем грубо, то работает это так:
1. делается выборка данных (без учета логики столбцов с OVER`ами)
2. по выбранным данным рассчитываются поля с OVER`ами
2.1 вся выборка из п.1 разделяется на группы (окна) по правилам указанным в условии PARTITION, в нашем случае это по годам, т.е. как бы группируется
2.2 при необходимости полученное окно сортируется для каждой строки
2.3 по полученному окну рассчитывается агрегатная функция
3 фильтруем выборку по краевым значениям

в 2.1 и 2.2:
для строки id=1 при расчете столбца "a" окно будет представлять из себя только одну строку (саму себя id = 1)
для строки id=2 при расчете столбца "a" окно будет уже шире - две строки (id=1, id=2)
и т.д.
при расчете столбца "d" при росте id окно будет наоборот уменьшаться, т.к. сортировка в окне обратная
в 2.3 функция count подсчитывает высоту окна
в 3 мы берем строки со значениями 1 в столбце "a" (первая строка года) и 1 в столбце "d" последняя строка года
вот результат подзапроса item_codes_ordered

id  t           item_code  a  d
 1  04.02.2008  11         1  4
 2  04.04.2008  12         2  3
 3  04.06.2008  13         3  2
 4  04.09.2008  14         4  1
 5  04.02.2009  21         1  4
 6  04.04.2009  22         2  3
 7  04.06.2009  23         3  2
 8  04.09.2009  24         4  1
 9  04.02.2010  31         1  4
10  04.04.2010  32         2  3
11  04.06.2010  33         3  2
12  04.09.2010  34         4  1