Find sum of Current month and Previous month values

user_6926998 0
Viewed 1843

I have a source table with employee account details for each month, date is String type (yyyyMMdd). Trying to find the sum of current month values and previous month values for each account.

Source data:

+-----------+-------------+-----------+----------+
|  date     | account     | division  |  amount  |
+-----------+-------------+-----------+----------+
| 20190331  | 123         | AB0       | 100      |
+-----------+-------------+-----------+----------+
| 20190331  | 123         | AB1       | 110      |
+-----------+-------------+-----------+----------+
| 20190331  | 123         | AB2       | 120      |
+-----------+-------------+-----------+----------+
| 20190228  | 123         | AB4       | 100      |
+-----------+-------------+-----------+----------+
| 20190228  | 123         | AB1       | 100      |
+-----------+-------------+-----------+----------+
| 20190228  | 123         | AB2       | 100      |
+-----------+-------------+-----------+----------+
| 20190131  | 123         | AB0       | 100      |
+-----------+-------------+-----------+----------+

ran below query in impala but this is returning me same results for current as well as previous month.

select distinct * from (
SELECT 
sum(amount) over (partition BY account, a.date) AS asset_current,
sum(amount) over (partition BY account, from_unixtime(unix_timestamp(to_date(LAST_DAY(ADD_MONTHS(to_timestamp(data_as_of_date,'yyyyMMdd'),-1))),'yyyy-MM-dd'),'yyyyMMdd')) AS asset_previous,
     account,
     date,
FROM employee_assets a
)x ;

Expected Output:

+-----------+-------------+--------------------+----------------------+
|  date     | account     | current_month_sum  |  previous_month_sum  |
+-----------+-------------+--------------------+----------------------+
| 20190331  | 123         | 330                | 300                  |
+-----------+-------------+--------------------+----------------------+
| 20190228  | 123         | 300                | 100                  |
+-----------+-------------+--------------------+----------------------+
| 20190131  | 123         | 100                | 0                    |
+-----------+-------------+--------------------+----------------------+

I have used the below query but it is returning asset_previous as month before if the previous month data is not available.

SELECT
    x.*,
    LAG(current_month_sum, 1, 0) OVER(PARTITION BY account ORDER BY adate) previous_month_sum  
FROM (
    SELECT adate, account, SUM(amount) current_month_sum  
    FROM employee_assets
    GROUP BY adate, account
) x
ORDER BY adate DESC

for example: We don't have input data for 20181231 for account 123, so asset_prev for month January should be 0, but query is returning 500 (which is amount for November'2018) Input data:

+-----------+-------------+-----------+----------+
|  date     | account     | division  |  amount  |
+-----------+-------------+-----------+----------+
| 20190331  | 123         | AB0       | 100      |
+-----------+-------------+-----------+----------+
| 20190331  | 123         | AB1       | 110      |
+-----------+-------------+-----------+----------+
| 20190331  | 123         | AB2       | 120      |
+-----------+-------------+-----------+----------+
| 20190228  | 123         | AB4       | 100      |
+-----------+-------------+-----------+----------+
| 20190228  | 123         | AB1       | 100      |
+-----------+-------------+-----------+----------+
| 20190228  | 123         | AB2       | 100      |
+-----------+-------------+-----------+----------+
| 20190131  | 123         | AB0       | 100      |
+-----------+-------------+-----------+----------+
| 20181130  | 123         | ABX       | 500      |
+-----------+-------------+-----------+----------+

Query is returning:

+-----------+-------------+--------------------+----------------------+
|  date     | account     | current_month_sum  |  previous_month_sum  |
+-----------+-------------+--------------------+----------------------+
| 20190331  | 123         | 330                | 300                  |
+-----------+-------------+--------------------+----------------------+
| 20190228  | 123         | 300                | 100                  |
+-----------+-------------+--------------------+----------------------+
| 20190131  | 123         | 100                | 500                  |
+-----------+-------------+--------------------+----------------------+
| 20191131  | 123         | 500                | 0                    |
+-----------+-------------+--------------------+----------------------+

Expected output:

+-----------+-------------+--------------------+----------------------+
|  date     | account     | current_month_sum  |  previous_month_sum  |
+-----------+-------------+--------------------+----------------------+
| 20190331  | 123         | 330                | 300                  |
+-----------+-------------+--------------------+----------------------+
| 20190228  | 123         | 300                | 100                  |
+-----------+-------------+--------------------+----------------------+
| 20190131  | 123         | 100                | 0                    |
+-----------+-------------+--------------------+----------------------+
| 20191131  | 123         | 500                | 0                    |
+-----------+-------------+--------------------+----------------------+
impala hive oracle
1 Answers

You can use aggregation in an inner query, and use LAG() in the outer query to get the previous month's value in the account partition. The three-arguments form of LAG() lets you specify a default value.

SELECT
    x.*,
    LAG(current_month_sum, 1, 0) OVER(PARTITION BY account ORDER BY adate) previous_month_sum  
FROM (
    SELECT adate, account, SUM(amount) current_month_sum  
    FROM employee_assets
    GROUP BY adate, account
) x
ORDER BY adate DESC

Note: date is not a good choice for a column name, since it might clash with a reserved word. I renamed that column to adate in the query.

user_106767160
Related