How to get previous value from table in sql-CodePudding

here is my database table:

Type	Qty	total	time
null	null	null	8:00:00
null	null	null	8:15:00
orange	2	2	8:30:00
orange	5	7	8:45:00
apple	10	10	9:00:00
orange	5	12	9:15:00
null	null	null	9:30:00
apple	8	18	9:45:00
orange	4	16	10:00:00

I want the result like the table below which is if the data is null then take the previous result:

Type	Qty	total	time
orange	0	0	8:00:00
apple	0	0	8:00:00
orange	0	0	8:15:00
apple	0	0	8:15:00
orange	2	2	8:30:00
apple	0	0	8:30:00
orange	5	7	8:45:00
apple	0	0	8:45:00
orange	5	7	9:00:00
apple	10	10	9:00:00
apple	10	10	9:15:00
orange	5	12	9:15:00
apple	10	10	9:30:00
orange	5	12	9:30:00
apple	8	18	9:45:00
orange	5	12	9:45:00
apple	8	18	10:00:00
orange	4	16	10:00:00

Does anyone know how to do it? Thanks in advance.

CodePudding user response：

We can use a calendar table approach here:

SELECT t1.Type,
       COALESCE(t3.Qty, 0) AS Qty,
       COALESCE(t3.total, 0) AS total,
       t2.time
FROM (SELECT DISTINCT Type FROM yourTable) t1
CROSS JOIN (SELECT DISTINCT time FROM yourTable) t2
LEFT JOIN yourTable t3
    ON t3.Type = t1.Type AND
       t3.time = t2.time
ORDER BY
    t2.time,
    t1.Type;

The approach here is to cross join subqueries which find all types and times. We then left join this to your table, filling in zero for any missing quantity or total. Note that if you already maintain separate tables for all types and/or times, you should use them in place of the subqueries aliased as t1 and t2.

CodePudding user response：

The most common method to fetch previous row value in SQL is by using the LAG function. The LAG function returns the previous row value. Depending on the order by clause the previous row is determined and the column value is returned.

SELECT value - lag(value) OVER (ORDER BY Id) FROM table

CodePudding user response：

Mysql 8.0

WITH a AS (SELECT COALESCE(Type, 'orange') AS Type, Qty, total, time
           FROM tcbi_test.demo1
           UNION
           SELECT COALESCE(Type, 'apple') AS Type, Qty, total, time
           FROM tcbi_test.demo1)
SELECT Type
     , COALESCE(Qty, LAG(Qty) OVER (PARTITION BY Type ORDER BY time), 0)   AS Qty
     , COALESCE(total, LAG(total) OVER (PARTITION BY Type ORDER BY time), 0) AS total
     , time
   FROM a
ORDER BY time

参考：MySQL | LEAD() and LAG() Function - GeeksforGeeks

CodePudding user response：

MySql does not support the IGNORE NULLS clause after ORDER BY in window functions, which would be very useful in this case, so I use correlated subqueries to fetch the last non-null value for Qty and total:

WITH types AS (SELECT DISTINCT Type FROM tablename WHERE type IS NOT NULL),
     times AS (SELECT DISTINCT time FROM tablename),
     cte AS (
       SELECT tp.type, t.Qty, t.total, tm.time
       FROM types tp CROSS JOIN times tm
       LEFT JOIN tablename t ON t.Type = tp.type AND t.time = tm.time
     )
SELECT c1.type,
       COALESCE(c1.Qty, (SELECT c2.Qty FROM cte c2 WHERE c2.Type = c1.Type AND c2.time < c1.time ORDER BY c2.time DESC LIMIT 1), 0) Qty,
       COALESCE(c1.total, (SELECT c2.total FROM cte c2 WHERE c2.Type = c1.Type AND c2.time < c1.time ORDER BY c2.time DESC LIMIT 1), 0) total,
       c1.time
FROM cte c1 
ORDER BY c1.time, c1.type;

See the demo.