I have a single table that looks like this:
| DATE | COUNTRY | CUSTOMER_TYPE |
| ------------ | ------------ | ------------ |
| 01/03/23 | Germany | 1 |
| 01/03/23 | Germany | 1 |
| 02/03/23 | France | 3 |
| 05/03/23 | UK | 1 |
| 10/03/23 | Spain | 5 |
| 31/03/23 | Germany | 2 |
My requirement is as follows:
- Group the table by month + year, country, customer type
- Add a column [OPENING_VALUE] counting all rows that have a date of the first of the month (grouped by the month, country and customer type)
- Add a column [CLOSING_VALUE] counting all rows that have a date of the last of the month (grouped by the month, country and customer type)
- Add a column [AVERAGE_VALUE] that averages the [OPENING_VALUE] and [CLOSING_VALUE] for each month.
It should look something like this:
| DATE | COUNTRY | CUSTOMER_TYPE | OPENING_VALUE | CLOSING_VALUE | AVERAGE_VALUE |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 03/23 | Germany | 1 | 2 | 1 | 1.5 |
| 03/23 | France | 1 | 17 | 25 | 21 |
| 03/23 | UK | 1 | 100 | 150 | 125 |
| 03/23 | Spain | 1 | 5 | 10 | 7.5 |
| 04/23 | Germany | 2 | 15 | 20 | 17.5 |
| 04/23 | France | 2 | 50 | 100 | 75 |
...
Thanks!
2条答案
按热度按时间ztigrdn81#
Here is how you find the "first" and "last" day of the month (assuming these are the first and last referenced days since day 1 would always be the first day but isn't in your example data.)
Now that you know the date of the first and the last items you should be able to do the group by
NOTE: It is not exactly clear to me if I am using the right partition and joins. This depends on what drives the uniqueness of your data model.
qkf9rpyu2#
It's really helpful if you provide DDL/DML that's easy to reproduce when you ask questions like this. Your result set doesn't seem to match the example data you provided.
Consider:
Using this we just need to be able to flag the first and last day values, and then aggregate on that:
As a side note, when using dates it's a good idea to use the ISO format of
yyyy-mm-dd
as it removes any ambiguity.