SQL sumarize subtotal depending on specific column and date

3
votes

please I need a master in SQL to solve the next. I have a simple (resumed from the real) table as this, from columns name to value, and I need to calculate keyval column as follow:

name period type value keyval formula RULE
n1 202105 ppto 123 1087 =123+876+88 If type='ppto' and if period between march to december then Sum value from current row to 2 preceding rows of type=Ppto and put in keyval column
n1 202104 ppto 876 975 =876+88+11 If type='ppto' and if period between march to december then Sum value from current row to 2 preceding rows of type=Ppto and put in keyval column
n1 202103 ppto 88 209 =88+11+110 If type='ppto' and if period between march to december then Sum value from current row to 2 preceding rows of type=Ppto and put in keyval column
n1 202102 ppto 11 134 =11+110+13 If type='ppto' and if period = february then Sum value from current row to 1 preceding rows of type=Ppto plus value from december of the last year of type=real and put in keyval column
n1 202101 ppto 110 166 =110+13+28 If type='ppto' and if periodo = january then Sum value from row type=Ppto plus values from december and november of the last year of type=real and put in keyval column
n1 202012 ppto 82 238 =82+55+101 If type='ppto' and if period between march to december then Sum value from current row to 2 preceding rows of type=Ppto and put in keyval column
n1 202011 ppto 55 258 =55+101+102 If type='ppto' and if period between march to december then Sum value from current row to 2 preceding rows of type=Ppto and put in keyval column
n1 202010 ppto 101 - =101+102+null null because there are not enough 3 values to sum (current to 2 preceding from type=ppto and period from month january to december)
n1 202009 ppto 102 - =102+null+null null because there are not enough 3 values to sum (current to 2 preceding from type=ppto and period from month january to december)
n1 202012 real 13 135 =13+28+94 If type='real' then Sum values from current row to 2 preceding rows of type=real and put in keyval column
n1 202011 real 28 160 =28+94+38 If type='real' then Sum values from current row to 2 preceding rows of type=real and put in keyval column
n1 202010 real 94 - =94+38+null null because there are not enough 3 values to sum (current to 2 preceding from type=real and from month january to december)
n1 202009 real 38 - =38+null+null null because there are not enough 3 values to sum (current to 2 preceding from type=real and from month january to december)

This is my best try to fit the solution, but I think it's very inefficient and not null values is getting when needed:

with b as ( 
    SELECT cast( substr(cast(period as string),1,4) as int64) as ano ,p.* FROM mytable p)
, ppto as (
    select b.* from b where type='ppto')
, real as (
    select sum(value) over (order by period desc rows between current row and 2 following) as keyval,b.* from b where type='real')
, both_sets as (
    select  p,r12,r11
    from ppto p
    left join real r12 on p.name = r12.name and r12.ano = p.ano-1 and cast( substr(cast(r12.period as string),5) as int64) = 12
    left join real r11 on p.name = r11.name and r11.ano = p.ano-1 and cast( substr(cast(r11.period as string),5) as int64) = 11)
, cat as(
    select
        (case when p.type='ppto' and cast( substr(cast(p.period as string),5) as int64) >2 then sum(p.value) over (order by p.period desc rows 
            between current row and 2 following)
          when p.type='ppto' and cast( substr(cast(p.period as string),5) as int64) =2 then sum(p.value) over (order by p.period desc rows 
            between current row and 1 following)+r12.value
          when p.type='ppto' and cast( substr(cast(p.period as string),5) as int64) =1 then p.value+r12.value+r11.value 
          else 0 end) keyval
    ,p.value ,p.period,p.name,p.type
    from both_sets u)
select * from cat
union all
select  keyval, value, period, name, type from real
order by type, period desc

And the result was this:

name period type value keyval
n1 202105 ppto 123 1087
n1 202104 ppto 876 975
n1 202103 ppto 88 209
n1 202102 ppto 11 134
n1 202101 ppto 110 166
n1 202012 ppto 82 238
n1 202011 ppto 55 258
n1 202010 ppto 101 203
n1 202009 ppto 102 102
n1 202012 real 13 135
n1 202011 real 28 160
n1 202010 real 94 132
n1 202009 real 38 38

As yo can see, these values aren't I need

Please, how Can I achieve this? I'll be very grateful for your time and help.

3
sqlpostgresqlgoogle-cloud-platformgoogle-bigquery
Are you using Postgres or BigQuery? Please tag only with the database you are really using. - Gordon Linoff
A hint: you're performing several different operations, each of them on a different subset of the data - so you might want to write several separate queries, each of them with a different WHERE clause, but all of them returning the same set of fields... Then you can join up the results sets with UNION. - Nigel Heffernan

3 Answers

1
votes

Consider below

select * except(month), 
  case 
    when type = 'real' or (type = 'ppto' and month between 3 and 12) then
        if(count(value) over recent3months < 3, null, sum(value) over recent3months)
    when type = 'ppto' and month = 2 then
        sum(value) over recent2months + sum(if(type = 'real', value, 0)) over recent3rdmonth
    when type = 'ppto' and month = 1 then
        value + sum(if(type = 'real', value, 0)) over recent2ndand3rdmonth
  end as keyval
from `project.dataset.mytable`, unnest([period - 100 * div(period, 100)]) month
window 
  recent3months as (partition by name, type order by period desc range between current row and 2 following),
  recent2months as (partition by name, type order by period desc range between current row and 1 following),
  recent3rdmonth as (partition by name order by period desc range between 90 following and 90 following),
  recent2ndand3rdmonth as (partition by name order by period desc range between 89 following and 90 following)

If applied to sample data in your question - output is

enter image description here

0
votes

Try LEAD for BigQuery:

select
  period,
  type,
  value,
  value + ifnull(LEAD(value) OVER (PARTITION BY type ORDER BY period DESC), 0)  + ifnull(LEAD(value, 2) OVER (PARTITION BY type ORDER BY period DESC), 0) as keyval
from mytable
order by type asc, period desc
0
votes

This is my new best try....

with b as ( SELECT cast( substr(cast(period as string),1,4) as int64) as ano ,p.* FROM `tc-sc-bi-bigdata-fdp-dev.stg_cor_cor_fdp_fnrp_dev.tmp_prueba` p)
, ppto as (
    select b.* from b where type='ppto')
, real as (
    select 
    (case when NTH_VALUE(value,3) OVER (PARTITION BY type ORDER BY period DESC rows between current row and 2 following) is not null then 
        sum(value) over (order by period desc rows between current row and 2 following) end) as keyval,b.* from b where type='real'
)
, unidos as (
select 
p,r12,r11
from ppto p
left join real r12 on p.name = r12.name and r12.ano = p.ano-1 and cast( substr(cast(r12.period as string),5) as int64) = 12
left join real r11 on p.name = r11.name and r11.ano = p.ano-1 and cast( substr(cast(r11.period as string),5) as int64) = 11)
, cat as(
    select
(case when p.type='ppto' and cast( substr(cast(p.period as string),5) as int64) >2 
            and NTH_VALUE(p.value,3) OVER (order by p.period desc rows between current row and 2 following) is not null
                then sum(p.value) over (order by p.period desc rows between current row and 2 following)
      when p.type='ppto' and cast( substr(cast(p.period as string),5) as int64) =2 
            and NTH_VALUE(p.value,2) OVER (order by p.period desc rows between current row and 1 following) is not null
                then sum(p.value) over (order by p.period desc rows between current row and 1 following)+r12.value
      when p.type='ppto' and cast( substr(cast(p.period as string),5) as int64) =1 then p.value+r12.value+r11.value 
      else null end) keyval
,p.value ,p.period,p.name,p.type
from unidos u)
select * from cat
union all
select  keyval, value, period, name, type from real
order by type, period desc