Привет, ночной. Решаю некоторые задачи по SQL, а точнее sqlite3. Сам знаю язык на уровне select, join и немного оконных функций. Поэтому моих знаний не хватает и прошу помощи или совета по вопросу у анона.
Задача: есть столбцы client_id и date. В date хранятся даты в интервале от 01.02.2019 до 01.02.2020 т.е ровно за год. Каждая дата начинается только с первого числа месяца, например 01.02.2019, 01.03.2019, 01.04.2019 и т.д. Нужно найти все client_id, у которых есть дата для каждого месяца прошедшего интервала, т.е каждый из 13 месяцев. Я думаю это делать через group_by по клиентам и посчитать количество уникальных дат, соответственно у кого оно будет равно 13, тот подходит. Решение, очевидно, кривое, но другого пока не могу придумать.
>>250094800 хуевое решение, не понятно, во что развернется в итоге. Ебнутый оптимизатор может этот каунт криво развернуть и запускать для каждой строки результата, т.е. ты получишь пачку обращений к БД на пустом месте. Тогда лучше это поле отдельно посчитать в where и сравнивать уже с ним.
>>250094095 (OP) Лови еще больший омск: для каждого клиента делаешь выборку дат, из каждой извлекаешь номер месяца, их складываешь и сравниваешь с заранее посчитанным числом (которое получится, если сложить все твои 13 месяцев).
>>250094800 Годный совет, спасибо. Но не работает что-то, наверное подзапрос надо ебошить, может по этому. А я уже забыл как это делать. Щас буду вспоминать.
>>250094886 На предыдущей работе аналитик был, там в юпитере работал, а сейчас данные из экселя вообще, поэтому по старой памяти в sqlite сделал базу, залил данные и через пандас запросы делаю
вообще ебал я манагерскую работу, как же приятно сидеть писать код, гуглить проблемы. Так бы и вкатился куда нибуь в бэкенд, но в 24 не судьба похоже, когда знакомые уже по 300к получают, которые сразу после пту вошли в сферу
>>250094095 (OP) Если скорость нужда и данных миллионы, то делай юнион по каждой дате. Дальше смержишь, можешь параллелить говназапросы свои . Кароч по ррпинципу мап-ридуса. Груп всегда сосет бо квадратичный. Если данных до десятка лямов записей, то не еби се моск, всем насрать на твой "красивый" код.
>>250095258 Я в целом против подзапросов - у тебя этот select count(distinct date_new) запускается для каждой строки результата, т.е. 79 раз. При этом ты понимаешь, что он всегда возвращает один и тот же результат, т.е. это суходрочка. Будет в результирующей таблице миллион записей - будет тебе миллион вложенных циклов.
В идеале - вычитай в переменную и используй значение в запросе. Или группировку с rollup сделай, это всё равно по своей сути - оконная функция, но ты можешь сделать count(*) без группировки по id_client и с группировкой в одном selectе.
>>250095359 Данных всего 400к, нужно чтобы просто работало
В таком случае можно ехать дальше и добавлять следующую колонку. Есть столбец sum_payment. Для каждого клиента, включая условие из первой задачи, посчитать "средний чек за период". Неоднозначное описание как по мне, поэтому буду считать, что это просто ебнуть среднее за весь год для каждого клиента. Это легко и вроде справлюсь сам, а дальше уже не мой уровень будет
>>250095675 Можно через промежуточную таблицу сделать, как на стековерфлоу советуют. Но в целом можно и не оптимизировать заранее и сделать подзапрос в having - будет тормозить, заюзаешь дополнительно таблицу со значением.
С rollup я напутал, он добавляет строку к результату, а не столбец.
Ну а теперь "средняя сумма покупок за месяц". Если это значит, посчитать сумму за каждый прошедший месяц и затем взять среднее, то идей даже нет как это делать
>>250095675>>250095893 Во втором ответе ссылке на SO говрят про рид-онли переменные. WITH const AS (SELECT count(distinct date_new) AS date_count FROM transactions) SELECT id_client, count(distinct date_new) FROM transactions GROUP BY id_client HAVING count(distinct date_new) = const.date_count
Не получается мыслить языком запросов к бд, у меня в голове это выглядит как циклом пройтись по массиву и посчитать нужные суммы и все остальное. А как это перевести в SQL пока не доходит
>>250096255 это да, к декларативному описанию надо прийти, а потом еще раз прийти, когда поймешь, как там всё внутри работает и почему подзапросы в основном - зло.
>>250096249 Для каждого id_client есть много date_new и sum_payment, как на скрине, нужно для каждого месяца посчитать сумму sum_payment и затем взять среднее из средних. Например, (avg(за февраль 2019) + avg(за март 2019) + avg(за апрель 2019) .. + avg(за фефраль 2020) и поделить на количество месяцев, т.е 13. У каждого клиента есть операции в каждом месяце, т.к в первой задаче взяты только клиента с непрерывной историей за период
>>250096591 Что-то я не то написал, да. Сам не до конца понимаю что значит "средняя сумма покупок за месяц". Думаю это сумма покупок за каждый месяц / количество прошедших месяцев.
>>250096650 если просто "средняя сумма покупок за месяц", то бери месяц и считай среднее. Если для человека - добавь туда человека. т.е. у тебя все равно вариантов два.
date_new, avg(sum_payment) или id_client, date_new, avg(sum_payment)
Не выдумывай себе трудности, их тебе выдумает заказчик.
В целом задача "среднее за месяц" звучит странно потому что: >Это же тоже самое, что просто сумма всех покупок за период / 13 В таком случае остается просто для каждого месяца запилить отдельный столбец со средним за этот месяц. Но это опять же странно, ведь период мог состоять не из 13 месяцев а из 113 Ну хуй с ним. Буду делать
>>250098091 Вот так получилось. Добавил еще количество операций за период, судя по всему в count можно не передавать параметр, ведь не важно какой столбец считать?
На сегодня закончу, завтра меня таки ждет ранжирование по месяцам, по возрастам с шагом N лет, подсчет за квартал, пиздец.
Спасибо всем кто был в треде и особенно тем, кто помог! Завтра на вечернем создам еще раз. 15 лет кун приходи