Блог

У меня есть таблица из нескольких неполных записей, каждая из которых ссылается на определенного клиента — тип клиента (Тип), годовые закупки (A), местоположение клиента (B), вторичный подрядчик (C), частота (D) и т.д. Эти записи действуют как моментальные снимки несовершенной информации о каждом клиенте и им присваивается год и уникальный snapshotID (snapID).

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

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

Пример данных:

 [custID]    [Type]      [snapID]    [A]     [B]         [C]          [D]        [Year]
1025        InPerson    5           0       (NULL)      Cavs        Weekly      2020
1025        InPerson    6           275     (NULL)      Heat        Weekly      2019
1025        InPerson    7           600     Cleveland   Cavs        (NULL)      2018
7256        Online      8           (NULL)  Akron       (NULL)      (NULL)      2020
7256        InPerson    9           375     (NULL)      Nets        (NULL)      2020
7256        Online      10          400     (NULL)      Thunder     Monthly     2015
7256        InPerson    11          (NULL)  (NULL)      Magic       (NULL)      2014
 

Желаемый результат:

 [custID]    [Type]      [A] [AYear] [B]         [BYear] [C]     [CYear] [D]     [DYear]
1025        InPerson    275 2019    Cleveland   2018    Cavs    2020    Weekly  2020
7256        InPerson    375 2020    (NULL)      (NULL)  Nets    2020    (NULL)  (NULL)
7256        Online      400 2015    Akron       2020    Thunder 2015    Monthly 2014
 

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

Для точного синтаксиса обратитесь к документации вашей СУБД. Я бы определил такие функции, как

 create function maxAYear (
@custID int,
@type varchar(...)
)
return select max(Year)
       from your_table
       where custID = @custID
       and @type = type
       and A is not null
       and A <> 0

create function maxA (
@custID int,
@type varchar(...)
)
return select A
       from my_table
       where custID = @custID
       and type = @type
       and year = maxAYear(@custID, @type)
 

а затем создайте мою таблицу результатов как

 select custID, type, maxA(custID, type), maxAYear(custID, type), ...
from (
    select distinct custID, type
    from my_table
) a
 

Я знаю, что некоторые люди не рекомендуют использовать пользовательские функции. Но я использую их довольно часто без каких-либо проблем, и у них есть два преимущества:

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

Это отвечает на исходную версию вопроса.

В стандартном SQL вы можете использовать last_value(ignore nulls) :

 select t.*
from (select custid, type,
             last_value(a ignore nulls) over (partition by custid, type order by year) as a,
             last_value(case when a is not null then year end ignore nulls) over (partition by custid, type order by year) as ayear,
             last_value(b ignore nulls) over (partition by custid, type order by year) as b,
             last_value(case when b is not null then year end ignore nulls) over (partition by custid, type order by year) as byear,
             last_value(c ignore nulls) over (partition by custid, type order by year) as c,
             last_value(case when c is not null then year end ignore nulls) over (partition by custid, type order by year) as cyear,
             last_value(d ignore nulls) over (partition by custid, type order by year) as d,
             last_value(case when d is not null then year end ignore nulls) over (partition by custid, type order by year) as dyear,
             row_number() over (partition by custid, type order by year desc) as seqnum
      from t
     ) t
where seqnum = 1;
 

Вот скрипка db<> .

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