Compare all rooms to all other rooms (Cartesian product)

Question

I have attendance data that is stored like this:

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

I then need to see the difference in attendance for each time of day from the previous day for each room. The result would look like this:

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

The previous I was able to accomplish with this query:

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;

So now I have the difference in attendance. This is where it gets a little complicated now. Maybe first seeing what the final product I am after may clear it up:

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

The reason for getting the difference between dates is to see if the attendance increased or decreased from the previous day. We are not actually concerned with the actual number from the difference, we are just interested if it went up or it went down.

OccuranceCount field - If a room's attendance went up/down one day we are trying to see whether another rooms attendance went up/down the next day. This field is used then to count how many times room2 went up/down one day and that room1 went up/down the next day. So if we take the first row as an example it shows that room A1 morning attendance went up 1 time when room A2's morning attendance went up the previous day during the 3 day period.

Room1DirectionCount/Room2DirectionCount field - These fields simply show how many time each direction occurred for each room. So if in the time period of 100 days if room A1 increased attendance 60 times the count would be 60.

Since I am comparing all the rooms to each other I have tried to do a cross join to form a cartesian product but I have been unable to figure out how to do the join properly so it references the other room's previous day.

I am not sure why this question was marked as a duplicate of a question regarding pivot tables? I don't believe this question is answered by that.

Answer 1

I am having hard times figuring out the meaning of some of your columns in the second expected output. However, for what it's worth, here are some examples and demonstrations that might help you.

If you are using MySQL 8.0, you can use the wonderful window functions to access rows that are related to the current row. The following query returns your first expected output (although where there is no previous date, NULL is returned instead of 0, to distinguish from the case when the frequentation is the same as the previous day) :

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.

With older versions of mysql, you could use a self-LEFT JOIN to access the previous row :

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

See this MySQL 5.7 db fiddle.

Once you have a query that returns the attendance differences, you can easily see if it went up or down in an outer query. Consider, for example :

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;

See this db fiddle.

Answer 2

I'm not 100% certain I understand your question, and there isn't really enough sample data/expected output to be sure, but I think this query will give you the results you want. It uses a couple of CTE's: one to get the differences for each building/room/date/timeofday combination, and the second to sum those (for the RoomDirectionCount columns), then just counts grouped rows to get the OccurrenceCount column.

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

The output is too long to include here but I've created a demo on dbfiddle. Alternate demo on dbfiddle.uk

Note that I've used a WHERE a1.diff IS NOT NULL clause to exclude results from the first day, you could possibly put a COALESCE around the computation of diff in the atdiff table and then not use that.