How do you subtract two JSON INTs based on JSON date ranges?-CodePudding

I have a database that contains userName and entry_data (User Input in JSON that includes value and entryDate).

Here is an example of what the table looks like:

| userName | value | entryDate |
| ________ | _____ | __________|
| Aaron    | 1234  | 7/25/2022 |
| Aaron    | 6463  | 7/26/2022 |
| Aaron    | 8375  | 7/27/2022 |
| Aaron    | 2734  | 7/28/2022 |
| Aaron    | 4563  | 7/29/2022 |
| Aaron    | 7374  | 7/30/2022 |
| Aaron    | 8923  | 7/31/2022 |
| Aaron    | 6737  | 8/1/2022  |
| Aaron    | 1374  | 8/2/2022  |
| Aaron    | 1834  | 8/3/2022  |
| Aaron    | 3646  | 8/4/2022  |
| Aaron    | 7834  | 8/5/2022  |
| Aaron    | 2473  | 8/6/2022  |
| Aaron    | 4673  | 8/7/2022  |

I'm tasked with summing the values (totalValue), summing the values by week (7/25-7/31 = week1Value, 8/1-8/7 = week2Value) and finding the difference week over week (week2Value - week1Value = WoWDifference), so it'd look something like this:

| userName | totalValue | week1Value | week2Value | WoWDifference |
| ________ | __________ | __________ | __________ | _____________ |
| Aaron    |   76,071   |   39,666   |   36,405   |    -3,261     |

I'm unfamiliar with 'grouping' and subtracting the values within the date ranges. This is how I've grouped things so far:

SELECT
    userName,
    cast((entry_data ->> 'value') as Int) as totalValue,
    case
         when (entry_data ->> 'week1Value')::DATE BETWEEN '2022-07-25' AND '2022-07-31' then 'week1'
         when (entry_data ->> 'week2Value')::DATE BETWEEN '2022-08-01' AND '2022-08-07' then 'week2'
    end as timeframe,
FROM entry

Output:

| userName | totalValue | timeframe|
| ________ | __________ | ________ |
| Aaron    |   39,666   |  week1   |
| Aaron    |   36,405   |  week2   |

How do I pull the totalValue sum by week to get the difference between the weeks?

CodePudding user response：

try this:

SELECT
    userName,
    sum(entry_data ->> 'value') as totalValue,
    case when (entry_data ->> 'week1Value')::DATE BETWEEN '2022-07-25' AND '2022-07-31' 
             then sum(entry_data ->> 'value') AS week1 /* changed */
    case when (entry_data ->> 'week2Value')::DATE BETWEEN '2022-08-01' AND '2022-08-07' 
             then sum(entry_data ->> 'value') AS week2 /* changed */
FROM entry
group by userName /* changed */

CodePudding user response：

Another approach (similar to @Y Bai) assuming dates are continuous.

with data as
  (select *, ceil(row_number() over (order by 0)::decimal/7)::integer rn 
  from entry)
select username,sum(to_number(entry_data->>'value','9999999')) total_value,
  sum (case when rn = 1 then
  to_number(entry_data->>'value','9999999')
  else 0
end) week_1_total,
sum (case when rn = 2 then
  to_number(entry_data->>'value','9999999')
  else 0
end) week_2_total
from data
group by username;

Or, use below query using CROSSTAB -

SELECT *
FROM   crosstab(
   'SELECT username, total_value,
    week_no, total_weekly
    FROM  (
        with data as (
            select e.username, e.rn week_no,
            (select sum(to_number(entry_data->>$$value$$,$$9999999$$)) 
            from entry) total_value,
            sum(to_number(entry_data->>$$value$$,$$9999999$$)) total_weekly
            from 
            (select *, 
            ceil(row_number() over 
            (partition by username order by 0)::decimal/7)::integer rn 
            from entry) e
            group by
            e.username, total_value, e.rn
            )
        select username,
        week_no,
        total_value,
        total_weekly
        from data
        union all
        select username,
        (select max(week_no) from data)   
        row_number() over (partition by username order by 0),
        total_value,
        total_weekly - coalesce(lag(total_weekly) over (order by week_no), 0)
        from data ) x 
        ORDER  BY 1' 
        ,$$VALUES (1), (2), (3), (4), (5), (6)$$
   ) AS ct 
   ("username" text, 
   "total_value" int,
   "Week_1" int, 
   "Week_2" int, 
   "Week_3" int,
   "Weekdiff_1" int, 
   "Weekdiff_2" int, 
   "Weekdiff_3" int
);

Refer to fiddle here.

The above fiddle also explores case with extrapolated data using both crosstab and manual method for pivot'ing.

There still can be cases which above might not handle, such as if there are less then 7 rows or its not a full week, but it provides a generic approach and can be modified to cater to specific use-cases.

The hard-coding in values clause of crosstab can also be replaced with a query to generate a number list of double the values as there are number of weeks present for a given user name.