填充以前的值 [英] Filling with previous values

问题描述

让我们想象一下我有一个字段表，数量，价格，折扣。在此，数量中的某些条目对应于价格，折扣为0。我想知道如何将上一行的值复制到价格和折扣为条目0的行，这样数量将为0，但价格和折扣永远不会为0。
同样...！
i / p：

lets imagine that I have a table of fields quantity,price,discount. Here some of the entries in quantity are 0 correspondingly price and discount becomes 0. I would like to know how to copy the value of previous row to the row with entries 0 for price and discount, such that quantity will be 0 but price and discount shall never be 0. likewise...! i/p:

+----------+-------+----------+
| quantity | price | discount |
+----------+-------+----------+
|       12 |   360 |       10 |
|        0 |     0 |        0 |
|        3 |    90 |        5 |
|        0 |     0 |        0 |
+----------+-------+----------+

预期输出：

+----------+-------+----------+
| quantity | price | discount |
+----------+-------+----------+
|       12 |   360 |       10 |
|        0 |   360 |       10 |
|        3 |    90 |        5 |
|        0 |    90 |        5 |
+----------+-------+----------+

推荐答案

如果要访问上一行，可以使用 LAG 函数：

If you want to access the previous row, you can use the LAG function:

> SELECT * FROM t;
┌────┬──────────┬───────┬──────────┐
│ id │ quantity │ price │ discount │
├────┼──────────┼───────┼──────────┤
│  1 │       12 │   360 │       10 │
│  2 │        0 │     0 │        0 │
│  3 │        3 │    90 │        5 │
│  4 │        0 │     0 │        0 │
└────┴──────────┴───────┴──────────┘

> SELECT quantity,
         CASE price
           WHEN 0 THEN LAG(price) OVER (ORDER BY id) 
           ELSE price 
         END,
         CASE discount
           WHEN 0 THEN LAG(discount) OVER (ORDER BY id) 
           ELSE discount
         END
  FROM t;
┌──────────┬───────┬──────────┐
│ quantity │ price │ discount │
├──────────┼───────┼──────────┤
│       12 │   360 │       10 │
│        0 │   360 │       10 │
│        3 │    90 │        5 │
│        0 │    90 │        5 │
└──────────┴───────┴──────────┘
(4 rows)

请注意，仅获取前一行，而没有获取前一个非零行，因此您可能最终仍以折扣 0 c $ c>和/或价格。

Note that this only fetches the previous row, no the previous "non-zero" one, so you may still end up with rows having 0 for discount and/or price.

这篇关于填充以前的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！