Блог

Не могли бы вы помочь мне, пожалуйста, решить приведенную ниже задачу в SQL (MS SQL Server 2017). Это просто в Excel, но кажется очень сложным в SQL.

Есть таблица с клиентами и их действиями, разделенная по дням:

 client  1may 2may 3may 4may 5may other days
client1 0    0    0    0    0    ...
client2 0    0    0    0    0    ...
client3 0    0    0    0    0    ...
client4 1    1    1    1    1    ...
client5 1    1    1    0    0    ...
  

Необходимо создать ту же таблицу (с тем же количеством строк и столбцов), но преобразовать значения в новые в соответствии с правилом:
Значение текущего дня =

А) Если все повседневные значения за неделю до дня, включая текущее, = 1, то 1

Б) Если все повседневные значения за неделю до дня, включая текущее, = 0, то 0

C) Если значения отличаются, то мы оставляем статус предыдущего дня (если статус предыдущего дня неизвестен, например, клиент новый, тогда 0)

В Excel я делаю это, используя формулу: = IF (И (AF2 = AE2; AE2 = AD2; AD2 = AC2; AC2 = AB2; AB2 = AA2; AA2 = Z2); current_day_value; IF (previous_day_value = «»; 0; previous_day_value )).

Пример с файлом Excel прилагается.

Большое вам спасибо.

Во-первых, НИКОГДА НЕ рекомендуется иметь даты в виде столбцов.

Итак, шаг # 1 перенесите ваши столбцы в строки. В другом мире построить таблицу с тремя столбцами

 ```
client  date Value
client1 May1    0
client1 May2    0
client1 May3    0
....    ...     ..
client4 May1    1 
client4 May2    1 
client4 May3    1 
....    ...     ..
```
  

шаг № 2 выполните все необходимые вычисления, используя поле даты.

В принципе, вы всегда указываете статус предыдущего дня, в любом случае (кроме null).

Итак, я бы сделал что-то вроде этого (синтаксис oracle, работающий и в sql server), предположив, что первые столбцы равны 1may

 Insert into newTable (client, 1may,2may,....) select (client, 0, coalesce(1may,0), coalesce (2may,0), .... from oldTable;
  

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

Вам придется столкнуться с этим, потому что большинство брендов SQL не допускают «произвольного поворота», то есть вам нужно указать столбцы, которые вы хотите отобразить в сводке, в то время как Excel просто сделает это за вас. SQL может это сделать, но для этого требуется динамический SQL, который может довольно быстро стать довольно сложным и раздражающим.

Я бы посоветовал вам использовать sql только для построения данных, а затем Excel или SSRS (как у вас в TSQL), чтобы фактически выполнить визуализацию.

В любом случае. Я думаю, это делает то, что вы хотите:

 WITH Data AS (
    SELECT * FROM (VALUES 
        ('Client 1',CONVERT(DATE, '2020-05-04'),1)
    ,   ('Client 1',CONVERT(DATE, '2020-05-05'),1)
    ,   ('Client 1',CONVERT(DATE, '2020-05-06'),1)
    ,   ('Client 1',CONVERT(DATE, '2020-05-07'),0)
    ,   ('Client 1',CONVERT(DATE, '2020-05-08'),0)
    ,   ('Client 1',CONVERT(DATE, '2020-05-09'),0)
    ,   ('Client 1',CONVERT(DATE, '2020-05-10'),1)
    ,   ('Client 1',CONVERT(DATE, '2020-05-11'),1)
    ,   ('Client 1',CONVERT(DATE, '2020-05-12'),1)
    ,   ('Client 2',CONVERT(DATE, '2020-05-04'),1)
    ,   ('Client 2',CONVERT(DATE, '2020-05-05'),0)
    ,   ('Client 2',CONVERT(DATE, '2020-05-06'),0)
    ,   ('Client 2',CONVERT(DATE, '2020-05-07'),1)
    ,   ('Client 2',CONVERT(DATE, '2020-05-08'),0)
    ,   ('Client 2',CONVERT(DATE, '2020-05-09'),1)
    ,   ('Client 2',CONVERT(DATE, '2020-05-10'),0)
    ,   ('Client 2',CONVERT(DATE, '2020-05-11'),1)
    ) x (Client, RowDate, Value)
)

SELECT 
    Client
,   RowDate
,   Value
,   CASE 
        WHEN OnesBefore = DaysInWeek THEN 1
        WHEN ZerosBefore = DaysInWeek THEN 0
        ELSE PreviousDayValue
    END As FinalCalculation
FROM (
    -- This set uses windowing to calculate the intermediate values
    SELECT 
        *
        
        -- The count of the days present in the data, as part of the week may be missing we can't assume 7
        -- We only count up to this day, so its in line with the other parts of the calculation
    ,   COUNT(RowDate) OVER (PARTITION BY Client, WeekCommencing ORDER BY RowDate) AS DaysInWeek
        
        -- Count up the 1's for this client and week, in date order, up to (and including) this date
    ,   COUNT(IIF(Value = 1, 1, NULL)) OVER (PARTITION BY Client, WeekCommencing ORDER BY RowDate) AS OnesBefore
    
        -- Count up the 0's for this client and week, in date order, up to (and including) this date
    ,   COUNT(IIF(Value = 0, 1, NULL)) OVER (PARTITION BY Client, WeekCommencing ORDER BY RowDate) AS ZerosBefore
    
        -- get the previous days value, or 0 if there isnt one
    ,   COALESCE(LAG(Value) OVER (PARTITION BY Client, WeekCommencing ORDER BY RowDate), 0) AS PreviousDayValue
    FROM (
        -- This set adds a few simple values in that we can leverage later
        SELECT
            *
        ,   DATEADD(DAY, -DATEPART(DW, RowDate)   1, RowDate) As WeekCommencing
        FROM Data
    ) AS DataWithExtras
) AS DataWithCalculations
  

Поскольку вы не указали свой макет таблицы, я не знаю, какие имена таблиц и полей использовать в моем примере. Надеюсь, если это правильно, вы сможете выяснить, как щелкнуть по нему на месте с помощью того, что у вас есть — если нет, оставьте комментарий

Также отмечу, что я сделал это намеренно подробным. Если вы не знаете, что такое предложение «OVER», вам нужно немного почитать: https://www.sqlshack.com/use-window-functions-sql-server /. Суть в том, что они выполняют агрегирование без фактического объединения строк вместе.

Редактировать: Скорректировано вычисление, чтобы иметь возможность учитывать произвольное количество дней в неделе

Огромное спасибо всем, особенно Дэвиду и Массимо, которые побудили меня реструктурировать данные.

 --we join clients and dates each with each and label clients with 'active' or 'inactive'
with a as (
select client, dates
from (select distinct client from dbo.clients) a
cross join (select dates from dates) b
)
, b as (
select date
      ,1 end active
      ,client
from clients a
join dbo.dates b on a.id = b.id

)
select client
      ,a.dates
      ,isnull(b.active, 0) active
into #tmp2
from a
left join b on a.client= b.client and a.dates = b.dates


--declare variables - for date start and for loop
declare @min_date date = (select min(dates) from #tmp2);
declare @n int = 1
declare @row int = (select count(distinct dates) from #tmp2) --number of the loop iterations

--delete data from the final results
delete from final_results

--fill the table with final results

--run the loop (each iteration = analyse of each 1-week range)
while @n<=@row

begin

with a as (
--run the loop 
select client
      ,max(dates) dates
      ,sum (case when active = 1 then 1 else null end) sum_active
      ,sum (case when active = 0 then 1 else null end) sum_inactive
from #tmp2
where dates between dateadd(day, -7   @n, @min_date) and dateadd(day, -1   @n, @min_date) 
group by client
)
INSERT INTO [dbo].[final_results]
           (client
           ,[dates]
           ,[final_result])

select client
      ,dates
      ,case when sum_active = 7 then 1 --rule A
       when sum_inactive = 7 then 0 -- rule B
       else
       (case when isnull(sum_active, 0)   isnull(sum_inactive, 0) < 7 then 0
       else 
           (select final_result 
           from final_results b 
           where b.dates = dateadd(day, -1, a.dates) 
           and a.client= b.client) end
       ) end
from a

  set @n=@n 1

end

if object_id(N'tempdb..#tmp2', 'U') is not null drop table #tmp2