如何在 PostgreSQL 9.3 中对两列进行条件求和 [英] How to conditional sum two columns in PostgreSQL 9.3

问题描述

我有以下数据表:

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 

基本上，此表显示了每个市场每月的订单和发货量信息.month 列中的日期 01-01-2015 实际上代表 Jan 2015(整月).

Basically this table shows information about orders and shipments per market per month. The date 01-01-2015 in month column actually represents Jan 2015 (the whole month).

我想SUM每个市场的lefttoship_thismonth 与之前所有月份的每个月.这是必要的，因为有人可以在一月份下订单，而订单是在二月份提供的.所以我想知道我每月还需要运送多少件商品.

I want to SUM the lefttoship_thismonth per market for each month with all previous months. This is needed as someone can place order in January which was supplied in February. So I want to know how many items I still need to ship per month.

输出应该是:

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 列很难处理.

How can I do that? I have no idea how to sum this way and the month column is very hard to work with.

推荐答案

如果您的 PostgreSQL 版本(还)不支持窗口函数，您可以使用 SubQuery 来做到这一点:

You can do it with a SubQuery, if your versions of PostgreSQL does not (yet) allow for window functions):

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      |
|------------+----------+-----------+--------------+----------------------+------------|

如果您可以使用窗口函数(效率更高)，您可以执行以下操作:

If you can use Window functions (which are more efficient), you can do the following:

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 函数.

If your month column is a varchar (not a good idea), you can cast it to date, or use the to_date function.

这篇关于如何在 PostgreSQL 9.3 中对两列进行条件求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！