Как вывести список клиентов с непрерывной историей за год?

Question

[bgood]

Есть таблица "Transactions_info" в ней колонки (date_new, Id_check, ID_client, Count_products, Sum_payment)
Нужно вывести список клиентов с непрерывной историей за год (минимум 1 покупка в месяц), учитывая, что клиент может в 1 месяц сделать несколько покупок:

SELECT `ID_client` FROM transactions_info 
WHERE COUNT (MONTH(`date_new`))>=12

Comments

[cicatrix]

А что понимается под "непрерывной историей"?

[bgood]

cicatrix, имеется в виду, что клиент делал покупки каждый день

[Akina]

клиент может в 1 день сделать несколько покупок, а в другой день не сделать вовсе

Ну так это не непрерывная история получается...

Да и вообще, я как-то с трудом представляю идиота, который в каждый из 365 дней года хоть что-то, да купил...

[bgood]

Akina, согласен, таких маловато будет :)
но в том-то и задача, нужно определить таких уникалов, но в голову не приходит ничего универсального.

[bgood]

Приношу свои извинения, непрерывная история покупок это минимум 1 покупка в месяц

Answer 1

[Cairn]

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

SELECT ID_client, Avg_check, Total_sum, Purchases, ROUND(Total_sum/Purchases,2) AS Avg_transaction
FROM (
  WITH per_check_t AS (
    SELECT 
      FIRST(CONCAT(YEAR(date_new),"-",MONTH(date_new))) AS Month, 
      FIRST(CAST(date_new AS DATE)) AS Date, 
      Id_check, 
      FIRST(ID_client) AS ID_client, 
      ROUND(SUM(Sum_payment),2) AS Check_sum,
      COUNT(*) AS n_transactions
    FROM transactions
    GROUP BY Id_check)
  SELECT 
    ID_client, 
    COUNT(DISTINCT Month) AS Months,
    ROUND(AVG(Check_sum),2) AS Avg_check,
    ROUND(SUM(Check_sum),2) AS Total_sum,
    SUM(n_transactions) AS Purchases
  FROM per_check_t
  GROUP BY per_check_t.ID_client
  HAVING Months = 13
  ORDER BY Total_sum DESC) AS full_months

Answer 2

[Akina]

SELECT ID_client
FROM Transactions_info
WHERE date_new BETWEEN @first_day_of_period
                   AND @last_day_of_period
GROUP BY ID_client
HAVING COUNT(DISTINCT date_new) = DATEDIFF(@last_day_of_period, @first_day_of_period) + 1

Comments

[FanatPHP]

блин я до такого простого как COUNT(DISTINCT date_new) не додумался. что-то городил через подзапрос

[bgood]

спасибо, выглядит отлично

[bgood]

а если тоже самое, но при условии, что покупки совершались 12 месяцев подряд, то нужно колонку date_new преобразовать в месеца?

[FanatPHP]

тебе по одной покупке в месяц что ли теперь надо?

[Akina]

bgood,

а если тоже самое, но при условии, что покупки совершались 12 месяцев подряд, то нужно колонку date_new преобразовать в месеца?

Месяц и год. Для верного...

[bgood]

FanatPHP, ага, что бы как минимум 1 покупка в месяц, но каждый месяц, а то тех, кто каждый день покупает вообще не оказалось :)

Answer 3

[Алексей Дубовский]

with MyClients as
( select [ID_client], count(distinct cast([date_new] as date)) UnqDays
from [Transactions_info]
where date_new > dateadd(day,-365,getdate()) -- берём транзакции за последние 365 дней
group by [ID_client]
)
select *
from MyClients
where UnqDays>=364 --Сегодняшний день пропускаем.
/*
для Microsoft SQL Server 2005+
*/

Comments

[bgood]

Извините,
вариант 1. не менее 1 раза в день

[FanatPHP]

Это надо переместить в комментарии под вопросом

[Алексей Дубовский]

with MyClients as
( select [ID_client], count(distinct cast([date_new] as date)) UnqDays
from [Transactions_info]
where date_new > dateadd(day,-365,getdate()) -- берём транзакции за последние 365 дней

)
select *
from MyClients
where UnqDays>=364 --Сегодняшний день пропускаем.
/*
для Microsoft SQL Server 2005+
*/

[FanatPHP]

а это надо написать в ответе

[bgood]

FanatPHP, подкажите, как это сделать, пожалуйста

[FanatPHP]

так вот же тебе выше написали

[Алексей Дубовский]

FanatPHP, :) теперь понял. Первый блин комом.

[FanatPHP]

ещё не поздно всё исправить. отредактировать пост можно нажав кнопку с тремя точками.

вот представь кто-то будет гуглить такой же вопрос. попадет на эту страницу. и что он увидит? кучу непонятных вопросов?

[Алексей Дубовский]

FanatPHP, спасибо, бро.
всё поправил.

Answer 4

[cicatrix]

SELECT ID_client FROM 
(SELECT
ID_client, LAG(date_new, 365, 0) OVER (PARTITION BY ID_client ORDER BY date_new) diffdate
FROM Transactions_info) calcdiff
WHERE diffdate > 0

sqlfiddle.com/#!18/902cf/40

Вместо 365 вот здесь: LAG(date_new, 365, 0) надо поставить кол-во требуемых непрерывных дней

Answer 5

[bgood]

Вобщем сделал так
SELECT ID_client,
COUNT(DISTINCT month(date_new))>=12 AS month
FROM transactions_info
GROUP BY ID_client

но оно показывает всех клиентов, а нужно только тех кто >12, пробовал через WHERE

SELECT ID_client,
COUNT(DISTINCT month(date_new)) AS month
FROM transactions_info
WHERE month>=12
GROUP BY ID_client

но пишет, что month не существует.

Comments

[bgood]

SELECT ID_client
FROM transactions_info
WHERE COUNT(DISTINCT month(date_new))>=12
GROUP BY ID_client

так тоже ошибка