SQL Server : Групповые клиенты и подписка

ProgramBox

SQL Server : Групповые клиенты и подписка

Post author:admin
Запись опубликована:11 января, 2022
Post category:Вопросы по программированию

#sql #sql-server #tsql

Вопрос:

У меня есть стол, который выглядит примерно так. Как вы можете видеть , Идентификатор клиента имеет идентификатор 0 подписки 000 , Идентификатор клиента 1 имеет подписки 001 002 и 003 . Поскольку подписка 003 также привязана к идентификатору клиента 2 , они связаны. Думайте об этом как о семье, которая подписывается. Отдельный член семьи со своими индивидуальными идентификаторами может иметь несколько подписок и/или также быть частью чьей-либо подписки в той же семье.

 with cte as
(
select '000' as Subscription, 0 as Customer
union all
select '001' as Subscription, 1 as Customer
union all
select '002' as Subscription, 1 as Customer
union all
select '003' as Subscription, 1 as Customer
union all
select '003' as Subscription, 2 as Customer
union all
select '004' as Subscription, 2 as Customer
union all
select '005' as Subscription, 3 as Customer
union all
select '006' as Subscription, 4 as Customer
union all
select '006' as Subscription, 5 as Customer
union all
select '007' as Subscription, 1 as Customer
)
select * from cte

Следует отметить, что идентификаторы Клиента и подписки будут не в порядке.

Что мне нужно, так это следующая таблица в качестве вывода. Я хочу иметь возможность связывать подписки со связанными клиентами и клиентами со связанными подписками вместе. В некотором смысле я хочу назначить уникальный идентификатор каждой семье.

Заранее спасибо.

1. Можете ли вы предоставить свои образцы данных в удобном для использования формате, а не в виде изображения, пожалуйста?

2. @Larnu Извиняется, обновил вопрос ссылкой на SQLFiddle

3. Пожалуйста, не используйте изображение в качестве образца данных.

4. Я вставил в вопрос для вас примерные данные. Обратите внимание, вы же знаете, что SQL Server поддерживает VALUES табличные конструкции, по крайней мере , с 2008 года, верно? Больше не нужно использовать этот старый UNION ALL синтаксис для таких вещей.

Ответ №1:

Проще всего было бы создать новый столбец в вашей таблице.

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

      select subscription, customer, 
    (select top 1 customer 
    from cte cte2 
    where cte2.subscription = cte.subscription
order by customer) as Group
        from cte
        order by subscription, customer

Если вам нужна группа выше 0, добавьте 1 в встроенный выбор:

     select subscription, customer, 
        (select top 1 customer   1
        from cte cte2 
        where cte2.subscription = cte.subscription
order by customer) as group
            from cte
            order by subscription, customer

1. Мне больше не нужна колонка «Клиент», мне нужен уникальный идентификатор для каждой связанной информации. значения в столбце Группа являются образцовыми, а не основанными на столбце клиент

Ответ №2:

Вы можете использовать две Lag функции и объединить их, чтобы найти изменения для группы, как показано ниже:

 ;with cte as
(
select *
from 
(
    values('000' , 0),('001' , 1),('002' , 1),('003' , 1),('003' , 2),('004' , 2),('005' , 3),('006' , 4),('006' , 5),('007' , 7)
)as t (Subscription, Customer)
)
, cte2 as
(
    select *,case when (lag(Subscription) over (order by (select null))) = Subscription then 0 else 1 end change_sub
    ,case when (lag(Customer) over (order by (select null))) = Customer then 0 else 1 end change_cust
    from cte
)
, cte3 as
(
    select *, change_sub amp; change_cust comb_change from cte2
)

select Subscription, Customer, (select sum(comb_change) from cte3 where Subscription <= t.Subscription) Final_Group
from cte3 t

Пожалуйста, ознакомьтесь с db<>fiddle <>здесь.

1. Спасибо @sacse, но это не работает, если подписка или клиенты вышли из строя. например, если вы измените клиента на 007 1, он не появится в группе 2. он создает свою собственную группу

2. @KrazzyNefarious прав, потому что я думал, что в столбце Подписка/Клиент есть некоторый порядок, основанный на приведенных данных.

3. Я хотел бы, чтобы все было в порядке, но это всего лишь пример, который я показываю здесь, столбец подписки-большой, а столбец клиента-числовой(24,0), и они не находятся в каком-либо определенном порядке

4. @KrazzyNefarious Пожалуйста, добавьте также различные сценарии к вопросу, чтобы можно было обдумать все моменты, прежде чем погружаться. Я думаю, что если нет определенного порядка, определение групп может быть длительным процессом.

5. @KrazzyNefarious решает ли это проблему?

Ответ №3:

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

 SELECT  Subscription,
        Customer, 
        SUM(CASE WHEN Customer <> PrevCust AND Subscription = PrevSub then 0 
                   WHEN Customer <> PrevCust  THEN 1 END) OVER(ORDER BY  Customer) [Group]
FROM
(
    select *, LAG(Subscription,1, '') OVER(PARTITION BY Subscription ORDER BY Customer) PrevSub
        ,LAG(Customer,1, -1) OVER(ORDER BY Customer) PrevCust
    from cte
) X

Метки: SQL Server : Групповые клиенты и подписка