mysql - 将所有房间与所有其他房间进行比较(笛卡尔积)

Question

我有这样存储的出勤数据:

Building | Room | Date | Morning | Evening
------------------------------------------
BuildA     A1      1       10       15
BuildA     A1      2       20       35
BuildA     A1      3       30       15
BuildA     A2      1       60       30
BuildA     A2      2       30       10
BuildA     A2      3       40       20
BuildB     B1      1       20       25
BuildB     B1      2       10       35
BuildB     B1      3       30       10
BuildB     B2      1       15       25
BuildB     B2      2       25       35
BuildB     B2      3       25       15

然后我需要查看每个房间一天中每个时间与前一天的出勤率差异。结果将如下所示:

Building | Room | Date | Morning | Evening | MorningDiff | EveningDiff
-----------------------------------------------------------------------
BuildA     A1      1       10       15          0              0
BuildA     A1      2       20       35          10             20
BuildA     A1      3       30       15          10            -20
BuildA     A2      1       60       30          0              0
BuildA     A2      2       30       10         -30            -20
BuildA     A2      3       40       20          10             10
BuildB     B1      1       20       25          0              0
BuildB     B1      2       10       35         -10             10    
BuildB     B1      3       30       10          20            -25
BuildB     B2      1       15       25          0              0
BuildB     B2      2       25       35          10             10
BuildB     B2      3       25       15          0             -20

之前我能够通过这个查询完成:

select t.*,  
    COALESCE((`morning` - 
        (select `morning` 
        from data t2 
        where t2.date < t.date 
        and t2.room = t.room
        order by t2.date desc 
        limit 1 )) ,0)
    as MorningDiff,
       COALESCE((`evening` - 
        (select `evening` 
        from data t2 
        where t2.date < t.date 
        and t2.room = t.room
        order by t2.date desc 
        limit 1 )) ,0)
    as EveningDiff  
from data t
order by room,date asc;

所以现在我的出勤率有所不同。这是现在变得有点复杂的地方。也许第一次看到我想要的最终产品可能会清除它:

Building1 | Room1 | TimeOfDay1 | Direction1 | Building2 | Room2 | TimeOfDay2 | Direction2 | OccuranceCount | Room1DirectionCount | Room2DirectionCount
-----------------------------------------------------------------------------------------------------------------------------------------------------
BuildA       A1      Morning         Up        BuildA       A2      Morning       Up            1                     2                  1
BuildA       A1      Morning         Up        BuildA       A2      Morning       Down          1                     2                  1
BuildA       A1      Morning         Up        BuildA       A2      Evening       Up            1                     2                  1
.
.
.

获取日期之间的差异的原因是查看出勤率与前一天相比是增加还是减少。我们实际上并不关心差异的实际数字，我们只关心它是上升还是下降。

OccuranceCount 字段 - 如果某一天某个房间的出勤率上升/下降，我们将尝试查看另一个房间的出勤率在下 天是否上升/下降。然后使用此字段计算 room2 一天上升/下降的次数以及 room1 第二天上升/下降的次数。因此，如果我们以第一行为例，它显示 room A1 morning出勤率上升1时间room A2的 morning在这 3 天的时间里，出勤率比前天有所上升。

Room1DirectionCount/Room2DirectionCount 字段 - 这些字段仅显示每个房间每个方向出现的次数。因此，如果在 100 天的时间段内，如果 A1 房间的出席人数增加了 60 倍，则计数将为 60。

由于我将所有房间相互比较，所以我尝试进行交叉连接以形成笛卡尔积，但我一直无法弄清楚如何正确进行连接，因此它引用了前一天其他房间的情况。

我不确定为什么这个问题被标记为与关于数据透视表的问题重复？我不相信这个问题可以用那个来回答。

Answer 1

我不能 100% 确定我理解您的问题，并且没有足够的示例数据/预期输出来确定，但我认为此查询将为您提供所需的结果。它使用了几个 CTE:一个获取每个建筑物/房间/日期/时间组合的差异，第二个对它们求和(对于 RoomDirectionCount 列)，然后只计算分组的行以获得OccurrenceCount 列。

with atdiff AS (SELECT
                building, room, date, 'Morning' AS time_of_day,
                morning - lag(morning) over (partition by building, room order by date) AS diff
                from attendance
                UNION SELECT 
                building, room, date, 'Evening',
                evening - lag(evening) over (partition by building, room order by date) diff
                from attendance),
    dircounts AS (SELECT
                  building, room, time_of_day, SIGN(diff) AS direction, COUNT(*) AS DirectionCount
                  FROM atdiff
                  GROUP BY building, room, time_of_day, direction)
select a1.building AS Building1, 
       a1.room AS Room1, 
       a1.time_of_day AS TimeOfDay1, 
       (CASE SIGN(a1.diff) WHEN 1 THEN 'Up' WHEN -1 THEN 'Down' ELSE 'Unchanged' END) AS Direction1,
       a2.building AS Building2, 
       a2.room AS Room2,
       a2.time_of_day AS TimeOfDay2, 
       (CASE SIGN(a2.diff) WHEN 1 THEN 'Up' WHEN -1 THEN 'Down' ELSE 'Unchanged' END) AS Direction2,
       COUNT(*) AS OccurrenceCount,
       MIN(d1.DirectionCount) AS Room1DirectionCount,
       MIN(d2.DirectionCount) AS Room2DirectionCount
from atdiff a1
join atdiff a2 on a2.date = a1.date + 1 AND (a2.building != a1.building OR a2.room != a1.room)
JOIN dircounts d1 ON d1.building = a1.building AND d1.room = a1.room AND d1.time_of_day = a1.time_of_day AND d1.direction = SIGN(a1.diff)
JOIN dircounts d2 ON d2.building = a2.building AND d2.room = a2.room AND d2.time_of_day = a2.time_of_day AND d2.direction = SIGN(a2.diff)
where a1.diff is not NULL
group by Building1, Room1, TimeofDay1, Direction1, Building2, Room2, TimeOfDay2, Direction2
order by Building1, Room1, TimeofDay1 DESC, Direction1 DESC, Building2, Room2, TimeOfDay2 DESC, Direction2 DESC

输出太长，无法包含在这里，但我创建了一个 demo on dbfiddle .候补 demo on dbfiddle.uk

请注意，我使用了 WHERE a1.diff IS NOT NULL 子句来排除第一天的结果，您可以在计算diff 在 atdiff 表中，然后不使用它。