代码之家  ›  专栏  ›  技术社区  ›  user2924127

将所有房间与所有其他房间进行比较(笛卡尔产品)

  •  1
  • user2924127  · 技术社区  · 6 年前

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

    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
    .
    .
    .
    

    获得日期差异的原因是看出勤率是否比前一天增加或减少。我们实际上不关心实际数字的差异,我们只是感兴趣,如果它上升或下降。

    发生计数字段 -如果有一天某个房间的出勤率上升/下降,我们将尝试查看另一个房间的出勤率是否上升/下降 下一个 一天。该字段用于计算Room2一天上升/下降和第二天上升/下降的次数。因此,如果我们以第一行为例,它表明 room A1 morning 出席人数增加了 1 room A2 早晨 出席人数增加了 以前的 3天期间的第天。

    Room1方向计数/Room2方向计数字段 -这些字段只显示每个房间每个方向发生的时间。因此,如果在100天的时间段内,A1房间的出勤率增加60倍,则计数将为60。

    由于我比较了所有房间,所以我试图进行交叉连接以形成笛卡尔积,但我无法确定如何正确地进行连接,因此它引用了另一个房间的前一天。

    我不知道为什么这个问题被标记为有关数据透视表的问题的副本?我不相信这个问题是用那个来回答的。

    2 回复  |  直到 6 年前
        1
  •  1
  •   Nick SamSmith1986    6 年前

    我不能百分之百地肯定我理解你的问题,也没有足够的样本数据/预期输出可以确定,但我认为这个查询会给你想要的结果。它使用两个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 如果要从第一天排除结果,可以将 COALESCE 在计算周围 diff atdiff 然后就不用了。

        2
  •  1
  •   GMB    6 年前

    我很难理解你在 第二 预期输出。然而,对于它的价值,这里有一些例子和演示可能会帮助您。

    如果您使用的是MySQL8.0,那么您可以使用 window functions 访问与当前行相关的行。以下查询返回 第一 预期产量(尽管没有上一个日期, NULL 返回而不是 0 ,以区别于频率与前一天相同的情况):

    select 
        a.*, 
        morning - lag(a.morning) over (partition by a.building, a.room order by a.date) morning_diff,
        evening - lag(a.evening) over (partition by a.building, a.room order by a.date) evening_diff
    from attendance a
    order by a.building, a.room, a.date
    

    See the db fiddle .

    使用旧版本的MySQL,您可以使用- LEFT JOIN 要访问上一行:

    select 
        a.*,
        a.morning - a1.morning morning_diff,
        a.evening - a1.evening evening_diff
    from 
        attendance a
        left join attendance a1 
            on a1.building = a.building and a1.room = a.room and a1.date = a.date - 1
    order by a.building, a.room, a.date
    

    this MySQL 5.7 db fiddle .

    一旦您有了一个返回出勤差异的查询,您就可以很容易地看到它在外部查询中是上升还是下降。例如,考虑:

    select t.*, 
        case 
            when morning_diff is null then 'Unknown'
            when morning_diff = 0 then 'Even'
            when morning_diff > 0 then 'Up'
            when morning_diff < 0 then 'Down'
        end morning_direction,
            case 
            when evening_diff is null then 'Unknown'
            when evening_diff = 0 then 'Even'
            when evening_diff > 0 then 'Up'
            when evening_diff < 0 then 'Down'
        end evening_direction
    from (
        select 
            a.*, 
            morning - lag(a.morning) over (partition by a.building, a.room order by a.date) morning_diff,
            evening - lag(a.evening) over (partition by a.building, a.room order by a.date) evening_diff
        from attendance a
    ) t
    order by t.building, t.room, t.date;
    

    this db fiddle .