如何在PostgreSQL 9.3中对两列进行条件求和

Question

我有以下数据表格：

month      marketid   totalsold   totalshipped   lefttoship_thismonth
....
01-01-2015   1          100            50              50
01-01-2015   2          10             3               7
01-01-2015   3          0              0               0
01-02-2015   1          0              50             -50
01-02-2015   2          20             0               20
01-02-2015   3          0              0                0 

基本上，这个表格显示了每个市场每个月的订单和发货信息。在“月份”列中的日期01-01-2015实际上代表了整个月的Jan 2015。

我想对每个月的每个市场的lefttoship_thismonth进行总和，并加上所有之前的月份。这是必要的，因为有人可能在1月份下订单，但在2月份供货。因此，我想知道每个月仍需发运多少物品。

输出结果应该是：

month      marketid   totalsold   totalshipped   totallefttoship   TOTALLEFT
01-01-2015   1          100            50              50             50   
01-01-2015   2          10             3               7               7
01-01-2015   3          0              0               0              0
01-02-2015   1          0              50             -50             0    /50-50
01-02-2015   2          20             0               20             27  /7+20
01-02-2015   3          0              0                0               0  / 0+0

我该怎么做呢？我不知道如何这样求和，而且month列很难处理。

- avi

Answer 1

Sum()Over() window aggregate function

SELECT "month",
       marketid,
       totalsold,
       totalshipped,
       lefttoship_thismonth,
       Sum(lefttoship_thismonth)OVER(partition BY marketid ORDER BY month ) AS TOTALLEFT
FROM   yourtable
ORDER  BY "month",
          marketid 

• 在线演示

- Pரதீப்

---

你比我稍微快了一点。 :) +1 - Gurwinder Singh

@avi，你的“month”列是什么类型？ - Gurwinder Singh

@avi - 还添加了演示。正如Gurv所问的那样，“Month”列的数据类型是什么？ - Pரதீப்

如果是varchar类型，您可以尝试在“order by”中使用to_date(month,'dd-mm-yyyy')而不是month。 - Gurwinder Singh

@Prdp月份列的类型为DATE。 - avi

@avi - 已添加演示。看看它是否给出了预期结果。 - Pரதீப்

Answer 2

如果您的PostgreSQL版本尚不支持窗口函数，您可以使用子查询来实现此操作：

WITH t (month, marketid, totalsold, totalshipped, lefttoship_thismonth) AS
(VALUES
    ('01-01-2015'::date,   1, 100, 50,  50),
    ('01-01-2015'::date,   2,  10,  3,   7),
    ('01-01-2015'::date,   3,   0,  0,   0),
    ('01-02-2015'::date,   1,   0, 50, -50),
    ('01-02-2015'::date,   2,  20,  0,  20),
    ('01-02-2015'::date,   3,   0,  0,   0) 
)

SELECT
    month, 
    marketid, 
    totalsold, 
    totalshipped, 
    lefttoship_thismonth, 
    (SELECT sum(lefttoship_thismonth) 
       FROM t t2 
      WHERE t2.marketid  = t1.marketid AND
            t2.month    <= t1.month
    ) AS total_left
FROM 
    t t1
ORDER BY
    month, marketid ;

这将获得以下结果：

|------------+----------+-----------+--------------+----------------------+------------|
|   month    | marketid | totalsold | totalshipped | lefttoship_thismonth | total_left |
|------------+----------+-----------+--------------+----------------------+------------|
| 2015-01-01 |    1     |    100    |      50      |          50          |     50     |
|------------+----------+-----------+--------------+----------------------+------------|
| 2015-01-01 |    2     |    10     |      3       |          7           |     7      |
|------------+----------+-----------+--------------+----------------------+------------|
| 2015-01-01 |    3     |     0     |      0       |          0           |     0      |
|------------+----------+-----------+--------------+----------------------+------------|
| 2015-01-02 |    1     |     0     |      50      |         -50          |     0      |
|------------+----------+-----------+--------------+----------------------+------------|
| 2015-01-02 |    2     |    20     |      0       |          20          |     27     |
|------------+----------+-----------+--------------+----------------------+------------|
| 2015-01-02 |    3     |     0     |      0       |          0           |     0      |
|------------+----------+-----------+--------------+----------------------+------------|

如果您可以使用窗口函数（它们更有效），则可以执行以下操作：

SELECT
    month, 
    marketid, 
    totalsold, 
    totalshipped, 
    lefttoship_thismonth, 
    ( sum(lefttoship_thismonth) 
           OVER (PARTITION BY marketid ORDER BY month ROWS UNBOUNDED PRECEDING)
    ) AS total_left
FROM 
    t t1
ORDER BY
    month, marketid ;

如果你的month列是一个varchar类型（不是个好主意），你可以将其转换为日期，或者使用to_date函数。

- joanolo

Answer 3

这段评论太长了。

如果该列是varchar类型，则必须将其转换为日期才能在order by子句中使用，如下所示。

select t.*,
  sum(totallefttoship) over 
   (partition by marketid order by to_date(month,'dd-mm-yyyy')) as TOTALLEFT
From yourtable t

- Gurwinder Singh