I have a sql code which looks like this:
Database: RedShift
WITH
X as
(
SELECT distinct pn , pg , ic, sr , cm, fq , m1 , m2 , m3 , m4
FROM table1 ORDER BY 1,2,3
),
table2 AS
(
Select g,p,t , avg(ss) as ss , avg(ce) as ce , sum(av) as ps
from
(
select distinct ic AS g , pn AS p , cm AS t , ss , cast((sum_m1/nullif(sum_m2,0)) as decimal(3,2)) as
ce , av
from
(
select *
, cast((sum(m3) over (partition by ic, pn,cm)) as decimal) as ss
, sum(m1) over (partition by ic, pn,cm) as sum_m1
, sum(m2) over (partition by ic, pn,cm) as sum_m2
, cast((avg(m2) over (partition by ic, pn,cm)) as decimal) as av
from X
ORDER BY 1,2,3
)
order by 1,2,3
)
where ss is not null
group by 1,2,3
order by 1,2,3
)
The group by value g,p,t changes every time and so it creates table for every new combination of g,p,t values.
One way to automate is dump this sql code in Python which might be inefficient:
Here is my approach-> I replace all values in list by {} braces
Example say:
I store all possible group by values in a list.
G=[g1,g2,g3]
P=[p1,p2,p3]
T=[t1,t2,t3]
Connection to databse:
c= psycopg2.connect(database=db,host=host,port=port,user=user,password=password,sslmode='require')
data2={}
for g in G:
for p in P:
for t in T:
sqlstr=( """ WITH
X as
(
SELECT distinct pn , pg , ic, sr , cm, fq , m1 , m2 , m3 , m4
FROM table1 ORDER BY 1,2,3
),
table2 AS
Select {},{},{} , avg(ss) as ss , avg(ce) as ce , sum(av) as ps
from
(
select distinct ic AS g , pn AS p , cm AS t , ss , cast((sum_m1/nullif(sum_m2,0)) as decimal(3,2)) as
ce , av
from
(
select *
, cast((sum(m3) over (partition by ic, pn,cm)) as decimal) as ss
, sum(m1) over (partition by ic, pn,cm) as sum_m1
, sum(m2) over (partition by ic, pn,cm) as sum_m2
, cast((avg(m2) over (partition by ic, pn,cm)) as decimal) as av
from X
ORDER BY 1,2,3
)
order by 1,2,3
)
where ss is not null
group by 1,2,3
order by 1,2,3
), select * from table2 """).format(g,p,t)
data2[g+"_"+p+"_"+t] = pd.read_sql_query(sqlstr, c)
Is there any better way to pass parameters as in above code sequence of {} should be maintained to pass parameters in order ??
Can we use some other approach other than SQL? In pythonic way?