Всем доброго дня, друзья. Я пока ещё нуб в sql запросах, прошу помочь или наставить на путь истинный.

Написал запрос

^{SELECT to_char(cc.date, 'YYYY-MM'), ca.company, sum(cc.total_with_vat), cc.account, cc.service
FROM cc
left join ca
on cc.account = ca.account
where cc.account like '200%' and cc.service ='IP' and ca.company like '%Новосибирск%'
group by to_char(cc.date, 'YYYY-MM'), cc.account, cc.service, ca.company}

Всё это выгружается в формате:
2016-01|Новосибирск|300|1|IP
2016-01|Новосибирск|500|2|IP
.. |Новосибирск|800|3|IP

Мне нужно эти данные выгрузить в эксель, проблема в том, чтобы выгрузить историю, необходимо выгружать около 10 раз, используя limit и offset, чтобы по количеству строк влазило на лист экселя

как можно сделать так, чтобы таблица выгружалась в формате, где даты будут в стобцах, то есть: 2016-01|2016-02 и т.д., а в строках соответственно остальные показатели, таким образом можно всё выгрузить за один запрос.

Почитал в интернете инфу, всё написано достаточно сложно для моего восприятия, поскольку я пока что не знаю многих тонкостей.

Если есть у кого свободные 10-15 минут, уделите время или киньте ссылок, где это более менее доступным языком описано (именно на базе postgresql)

982183,

поясните, если не сложно, я выгружаю через pg_admin в csv, потом когда открываю файл через эксель, он пишет, что данные обрезаны из-за того, что строк не хватает

Вообще конечно хочу научиться в формате сводной делать, но пока такой вариант тоже подойдёт

Щукина Анна,

спасибо, обязательно почитаю)

tip78,

запрос:

^{SELECT to_char(cc.date, 'YYYY-MM'), ca.company, sum(cc.total_with_vat), cc.account, cc.service
FROM cc
left join ca
on cc.account = ca.account
where cc.account like '200%' and cc.service ='IP' and ca.company like '%Новосибирск%'
group by to_char(cc.date, 'YYYY-MM'), cc.account, cc.service, ca.company}

Что выгружает:
to_char base_company sum account_number service2016-01 Сибирские Сети Новосибирск 300 2E+12 IP2016-01 Сибирские Сети Новосибирск 300 2E+12 IP2016-01 Сибирские Сети Новосибирск 635 2E+12 IP

Что нужно:

base_company account_number 2016-01 2016-02 2016-03 2016-04 2016-05 2016-06 2016-07 2016-08 2016-09 2016-10 2016-11Сибирские Сети Новосибирск 2E+12 300 300 300 300 300 300 300 300 300 300 300Сибирские Сети Новосибирск 2E+12 45039 8194 3175 635 635Сибирские Сети Новосибирск 2E+12 300 300 300 300 300 300 300 300 300 300 300

tip78,

запрос:

^{SELECT to_char(cc.date, 'YYYY-MM'), ca.company, sum(cc.total_with_vat), cc.account, cc.service
FROM cc
left join ca
on cc.account = ca.account
where cc.account like '200%' and cc.service ='IP' and ca.company like '%Новосибирск%'
group by to_char(cc.date, 'YYYY-MM'), cc.account, cc.service, ca.company}

Что выгружает:
to_char base_company sum account_number service2016-01 Новосибирск 300 2E+12 IP2016-01 Новосибирск 300 2E+12 IP2016-01 Новосибирск 635 2E+12 IP

Что нужно:

base_company account_number 2016-01 2016-02 2016-03 2016-04 2016-05 2016-06 2016-07 2016-08 2016-09 2016-10 2016-11Новосибирск 2E+12 300 300 300 300 300 300 300 300 300 300 300Новосибирск 2E+12 45039 8194 3175 635 635 Новосибирск 2E+12 300 300 300 300 300 300 300 300 300 300 300

tip78,

ну смотрите - есть список данных: дата, номер, сумма, регион

Эти данные хранятся в базе в таком формате, что на одну дату (и уж тем более на один месяц) приходится несколько сумм по номерам.
Мне нужно, чтобы на один месяц был один номер с общей суммой, поэтому я делаю сумм и группирую по дате, иначе выгрузиться намного больший массив по строкам.

Но поскольку этих номеров очень много, даже при учёте группировке, в идеале нужно, чтобы дата была в столбцах (аналогично сводной таблице), а поскольку я sql только вот начал изучать самостоятельно, я очень многого не знаю. В скриптах, которые я находил в интернете, попросту не могу разобраться, не хватает мне пока ещё знаний и тонкостей синтаксиса.

Во вложении скрин запроса и таблицы (на like можете не обращать внимание, это вывод номеров, которые мне нужны, этот оператор особой роли не играет)

982183,

Эксель 2016, в итоговом csv файле где-то 5-6 млн строк

tip78,

Если группировать по аккаунтам, то будет один аккаунт - одна сумма в общем за все периоды, а мне нужна один аккаунт - одна сумма на каждый месяц (то есть сумма за январь, февраль, март и т.д. по каждому аккаунту)

angel_zar,

Насчёт экселя, конечно проще, но мне в принципе и самому интересно, как можно строить сводные в самом SQLе

По поводу прицепить csv файл в качестве источника - интересный вариант, строк будет меньше миллиона, не знал, что так можно. Спасибо, попробую)

982183,

Не понимаю пока что - что это и что будет в результате, но спасибо, я попробую сейчас с этим разобраться)

От экселя никуда не уйти, да и куда уйти, я кроме экселя и гугл таблиц похожих интерфейсов не встречал, а конечные пользователи, для которых формируется отчёт - работают в экселе

982183,

Подскажите, пожалуйста, а после того, как все CASE заданы, далее я просто дописываю в SELECT то что мне нужно?

982183,

таким образом должно выглядеть? Оставшиеся колонки (сервис, компания и номер) просто дописываются или тоже через CASE?

982183,

SELECT
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2016-1" THEN churn_charges.total_with_vat END) as 2016-1,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2016-2" THEN churn_chargestotal_with_vat END) as 2016-2,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2016-3" THEN churn_charges.total_with_vat END) as 2016-3,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2016-4" THEN churn_charges.total_with_vat END) as 2016-4,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2016-5" THEN churn_charges.total_with_vat END) as 2016-5,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2016-6" THEN churn_charges.total_with_vat END) as 2016-6,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2016-7" THEN churn_charges.total_with_vat END) as 2016-7,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2016-8" THEN churn_charges.total_with_vat END) as 2016-8,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2016-9" THEN churn_charges.total_with_vat END) as 2016-9,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2016-10" THEN churn_charges.total_with_vat END) as 2016-10,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2016-11" THEN churn_charges.total_with_vat END) as 2016-11,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2016-12" THEN churn_charges.total_with_vat END) as 2016-12,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2017-1" THEN churn_charges.total_with_vat END) as 2017-1,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2017-2" THEN churn_charges.total_with_vat END) as 2017-2,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2017-3" THEN churn_charges.total_with_vat END) as 2017-3,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2017-4" THEN churn_charges.total_with_vat END) as 2017-4,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2017-5" THEN churn_charges.total_with_vat END) as 2017-5,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2017-6" THEN churn_charges.total_with_vat END) as 2017-6,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2017-7" THEN churn_charges.total_with_vat END) as 2017-7,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2017-8" THEN churn_charges.total_with_vat END) as 2017-8,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2017-9" THEN churn_charges.total_with_vat END) as 2017-9,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2017-10" THEN churn_charges.total_with_vat END) as 2017-10,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2017-11" THEN churn_charges.total_with_vat END) as 2017-11,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2017-12" THEN churn_charges.total_with_vat END) as 2017-12,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2018-1" THEN churn_charges.total_with_vat END) as 2018-1,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2018-2" THEN churn_charges.total_with_vat END) as 2018-2,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2018-3" THEN churn_charges.total_with_vat END) as 2018-3,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM'), ="2018-4" THEN churn_charges.total_with_vat END) as 2018-4,
churn_charges.account_number, churn_accounts.base_company, churn_charges.service
FROM public.churn_charges
left join churn_accounts
on churn_charges.account_number = churn_accounts.account_number
where churn_charges.account_number like '200%' and churn_charges.service ='IP' and churn_accounts.base_company like '%Новосибирск%'
group by churn_charges.account_number, churn_charges.service, churn_accounts.base_company;

982183,

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

SELECT
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM') = "2016-1" THEN churn_charges.total_with_vat END) as 2016-1,
churn_charges.account_number, churn_accounts.base_company, churn_charges.service
FROM public.churn_charges
left join churn_accounts
on churn_charges.account_number = churn_accounts.account_number
where churn_charges.account_number like '200%' and churn_charges.service ='IP' and churn_accounts.base_company like '%Новосибирск%'
and to_char(churn_charges.charge_date, 'YYYY-MM') between '2016-01-01' and '2016-01-31'
group by churn_charges.account_number, churn_charges.service, churn_accounts.base_company;

982183,

Попробовал, не работает пока, к сожалению. Ладно, посижу потыкаю код, что-нибудь да получится

Спасибо огромное вам, что уделили время и за помощь. Если получится, отпишу сюда, как оно должно всё же выглядеть)

982183,

а pg никаких ошибок не выдаёт, просто пустое поле, где должны быть итоговые данные запроса

982183,

Пробовал и с фильтром и без, кавычки тоже убирал, пока тишина)

982183,

Про минус что-то не понял

SELECT churn_charges.account_number,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM') = '2016-10' THEN churn_charges.total_with_vat END) as 2016-10,
sum(CASE WHEN to_char(churn_charges.charge_date, 'YYYY-MM') = '2016-11' THEN churn_charges.total_with_vat END) as 2016-11
FROM public.churn_charges
group by churn_charges.account_number;

Так всё равно не пашет, может в с датой что-то не так

982183,

ну и у меня стоит 10 версия, а не 9.6, хотя по идее вряд ли они синтаксисом могут отличаться

982183,

действительно, после этого запустился, сейчас запрос по всей истории запустить. Спасибо вам большое, что потратили столько времени и решили мою проблему, без вас бы я долго разбирался. Добра вам и позитива!)

982183,

хорошо, обязательно перепишу, пока что там просто много всего непонятного, когда читал первый раз об этом, со временем разберусь, мне это интересно