I have a table like this
It records the price of items among 7 days in a week if the item has selling record on that day. Thus, the item price on days that they don't get sold does not show up in this table, which are the records I want to add on. The output table I need is:
as you can see, the rows highlighted in yellow are the rows I added, to make each item has complete record in 7 days a week. and the price on the date I added is 0.
My idea is to create a table with 21 rows and 3 columns with predefined value for column 'keyword' and 'day', and null on price, and then join it with the original table. But my data is of large size, do you have any simpler method to suggest? Thanks a lot!
Is this just some example or your actual task?
What's the actual datatype of the DAY column, BYTEINT or a DATE?
How many rows exist?
It's my actual task, the actual datatype of the DAY column is BYTEINT.
The number of unique 'keyword' is about 10K, i.e there would be 10K * 7 rows in the expanded data set.
Your approach sounds ok, just do everthing in a single Select, create the matrix using a Cross Join and then Left Join to your existing data
You need a helper table with the numbers 1 to 7 in it.
SELECT k.keyword, nbr.i, Coalesce(t.price, 0) FROM -- of course you might have another table where you might get the keywords from ( SELECT DISTINCT keyword FROM tab ) AS k
-- the numbers table CROSS JOIN numbers AS nbr
LEFT JOIN tab AS t ON t.keyword = dt.keyword AND t.day = nbr.i