CODEWITHSUNDEEP
sqlpostgresqlaggregate-functions
as - if
as - if

Reputation: 3267

SQL percent of total and weighted average

I have the following postgreSql table stock, there the structure is the following:

| column |  pk |
+--------+-----+
| date   | yes |
| id     | yes |
| type   | yes |
| qty    |     |
| fee    |     |

The table looks like this:

|    date    |  id | type | qty  | cost |
+------------+-----+------+------+------+
| 2015-01-01 | 001 | CB04 |  500 |    2 |
| 2015-01-01 | 002 | CB04 | 1500 |    3 |
| 2015-01-01 | 003 | CB04 |  500 |    1 |
| 2015-01-01 | 004 | CB04 |  100 |    5 |
| 2015-01-01 | 001 | CB02 |  800 |    6 |
| 2015-01-02 | 002 | CB03 | 3100 |    1 |

I want to create a view or query, so that the result looks like this. The table will show the t_qty, % of total Qty, and weighted fee for each day and each type:

% of total Qty = qty / t_qty weighted fee = fee * % of total Qty

|    date    |  id | type | qty  | cost | t_qty | % of total Qty | weighted fee |
+------------+-----+------+------+------+-------+----------------+--------------+
| 2015-01-01 | 001 | CB04 |  500 |    2 |  2600 |           0.19 |         0.38 |
| 2015-01-01 | 002 | CB04 | 1500 |    3 |  2600 |           0.58 |         1.73 |
| 2015-01-01 | 003 | CB04 |  500 |    1 |  2600 |           0.19 |        0.192 |
| 2015-01-01 | 004 | CB04 |  100 |    5 |  2600 |           0.04 |        0.192 |
|            |     |      |      |      |       |                |              |

I could do this in Excel, but the dataset is too big to process.

Upvotes: 0

Views: 506

Answers (1)

D-Shih
D-Shih

Reputation: 46219

You can use SUM with windows function and some Calculation to make it.

SELECT *,
       SUM(qty) OVER (PARTITION BY date ORDER BY date) t_qty,
       qty::numeric/SUM(qty) OVER (PARTITION BY date ORDER BY date) ,
       fee * (qty::numeric/SUM(qty) OVER (PARTITION BY date ORDER BY date))
FROM T

If you want to Rounding you can use ROUND function.

SELECT *,
       SUM(qty) OVER (PARTITION BY date ORDER BY date) t_qty,
       ROUND(qty::numeric/SUM(qty) OVER (PARTITION BY date ORDER BY date),3) "% of total Qty",
       ROUND(fee * (qty::numeric/SUM(qty) OVER (PARTITION BY date ORDER BY date)),3) "weighted fee"
FROM T

sqlfiddle

[Results]:

|       date |  id | type |  qty | fee | t_qty | % of total Qty | weighted fee |
|------------|-----|------|------|-----|-------|----------------|--------------|
| 2015-01-01 | 001 | CB04 |  500 |   2 |  2600 |          0.192 |        0.385 |
| 2015-01-01 | 002 | CB04 | 1500 |   3 |  2600 |          0.577 |        1.731 |
| 2015-01-01 | 003 | CB04 |  500 |   1 |  2600 |          0.192 |        0.192 |
| 2015-01-01 | 004 | CB04 |  100 |   5 |  2600 |          0.038 |        0.192 |
| 2015-01-02 | 002 | CB03 | 3100 |   1 |  3100 |              1 |            1 |

Upvotes: 2

Related Questions