sql-server - 查询行之间的关系

Question

我需要在单个表中找到多个人之间的关系，例如我有下表：

宾客桌

所以我需要通过sql脚本说Guest 123和456他们在同一时间一起入住同一家酒店80％等等......

请支持。

标签： sql-serverdatabaserdbms

Answer 1

这有点复杂，所以我将它分解为多个子查询，供您使用具有匹配键的 CTE。

这将产生一系列匹配的对 - 主要客人和次要客人的比例是他们在一起的频率，而不仅仅是入住。

设置：

create table temp(
    hotelID integer,
    checkInDate date,
    guestID integer
)

insert into temp values (101, '2020/06/01', 123)
insert into temp values (101, '2020/06/01', 456)
insert into temp values (102, '2020/06/15', 123)
insert into temp values (102, '2020/06/15', 456)
insert into temp values (103, '2020/06/30', 123)
insert into temp values (103, '2020/06/30', 456)
insert into temp values (104, '2020/07/15', 123)
insert into temp values (104, '2020/07/15', 789)
insert into temp values (105, '2020/07/01', 456)
insert into temp values (105, '2020/07/01', 789)

询问：

    with keyCte as (
        select 
            distinct cast(hotelID as varchar(3)) + cast(checkInDate as varchar(10)) as myKey,
            guestID
        from temp
    ) 

    select 
          guestPrime
        , guestTwo
        , instances as guestPrimeStays
        , matches as guestTwoMatches
        , cast(matches as float) / cast(instances as float) as hitRate

    from (
            select 
                  guestID
                , count(*) as instances 
            from keyCte 
            group by guestID
         ) sq3

    join (
        select
            guestPrime
            , guestTwo
            , count(*) as matches
        from (
            select 
                keyCte.guestID as guestPrime
              , kcte.guestID as guestTwo
            from keyCte
            join keyCte kcte on kcte.myKey = keyCte.myKey and kcte.guestID != keyCte.guestID
        ) sq 
        group by guestPrime, guestTwo

    ) sq2 on sq2.guestPrime = guestID