How to return null on many to many join within window partition-CodePudding

I am creating a many to many join based on date, and then running a partition based on the user_id. The problem is that I need there to be an entry for each date, even if there is no data.

color_table

guess_table

Desired output (join with partition over user)

The problem is that, because user 1 didn't guess on 10-26-2021 and user 2 didn't guess on 10-28-2021, the rows with null simply don't exist. And I need the rows to be present as I'm doing operations within the window based on the date, so I need to see every date for a user, even if there's no guess.

Here is the fiddle with the window function and join: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=b9f7f9549a0793aa16335141d86b7352

Here is the call I'm trying:

select dayt, user, cnt, guess, ROW_NUMBER() OVER (PARTITION BY user ORDER BY dayt) AS rowi
from color_table 
left join guess_table 
on dayt = dait;

CodePudding user response：

Because the data you want to be shown does not exist for the left join / join operation you have to create it first. You do so by using a CROSS JOIN operation and then left joining with the existing tables:

select cj.dayt, cj.user, c2.cnt, 
       g2.guess, 
       ROW_NUMBER() OVER (PARTITION BY cj.user ORDER BY dayt) AS rowi
from 
   (select distinct c.dayt, g.user 
      from color_table c cross join guess_table g) cj
   left join color_table c2 on c2.dayt = cj.dayt
   left join guess_table g2 on g2.dait = cj.dayt and g2.user = cj.user;

See it working here: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1ee0ee72007d3d17e50beb990f6691c2

CodePudding user response：

You need all the combinations of the rows of color_table with each user and you can do this with a CROSS JOIN.
Then do a LEFT join to guess_table:

SELECT c.dayt, u.user, c.cnt, g.guess, 
       ROW_NUMBER() OVER (PARTITION BY u.user ORDER BY c.dayt) AS rowi
FROM color_table c
CROSS JOIN (SELECT DISTINCT user FROM guess_table) u
LEFT JOIN guess_table g
ON g.dait = c.dayt AND g.user = u.user;

See the demo.