问题描述:

I have a SQL query

 select time,t_count,sum(t_count) over (order by time) as

cumulative_t_count ,cumulative_t_count/sum(t_count) as percentage_cumulative count,state,hour from (select distinct

time,count(distinct num_id) as t_count,state,hour from (select * from

public.table1 where day=1 and time>0 order by time)as A group by

time,hour,state order by hour,state )B where state=1 and hour=1;

which gives me a results in the below format:

time t_count cumulative_t_count state hour

_____ _____ _________________ ____ ____

1 10 10 1 1

2 20 30 1 1

3 30 60 1 1

4 60 120 1 1

Likewise I have 80 state and hour from 0-23.I want to export all the results to CSV files by changing the state from 1-80 and for the hours 1,7,14,19.So now I am manually changing the state and hour in the above code and exporting the results as CSV.But it seems I have to change state 1 to 80 and hours 1,7,14,19 for each state.

BEGIN

FOR i IN 1..80 LOOP

FOR i IN 1,7,14,19 LOOP

/copy ( select time,t_count,sum(t_count) over (order by time) as

cumulative_t_count ,cumulative_t_count/sum(t_count) as percentage_cumulative count,state,hour from (select distinct

time,count(distinct num_id) as t_count,state,hour from (select * from

public.table1 where day=1 and time>0 order by time)as A group by

time,hour,state order by hour,state )B where state=i and hour=j)

To '/tmp/state_i_hour_j.csv' With CSV

END LOOP

END LOOP

But this did not work.I also want to export each result set in CSV format.Any help is appreciated.

网友答案:

If you only need to do this the once, you can use the following to create the sql statements you need. You can then copy the output records and execute them.

WITH states AS
(SELECT generate_series(1,80) as i),
hours AS
(SELECT j FROM (values (1),(14),(17),(19) ) s(j))
SELECT 'copy ( select time,t_count,sum(t_count) over (order by time) as
       cumulative_t_count ,cumulative_t_count/sum(t_count) as     percentage_cumulative count,state,hour from (select distinct
       time,count(distinct num_id) as t_count,state,hour from (select * from
       public.table1 where day=1 and time>0 order by time)as A group by
       time,hour,state order by hour,state )B where state=' ||i || ' and hour=' ||j || ')

     To ''/tmp/state_' ||i || '_hour_' ||j || '.csv'' With CSV;' from states,hours
相关阅读:
Top