Netezza For循环

Question

我有以下查询，我将称之为query1：

with a as (
select customer_key as cust,
sum(sales)*1.0/4 as avg_sales
sum(returns)*1.0/4 as avg_return
count(distinct order_key)*1.04 as avg_num_orders
from orders_table
where purch_year between 2011 and 2014
group by cust
order by random()
),

b as (
select *
from a
where avg_num_orders > .25
limit 100000
)

select case 
       when avg_num_orders <= 1 then 'Low'
       when avg_num_orders between 1 and 4 then 'Medium'
       when avg_num_orders > 4 then 'High'
   end as estimated_frequency,
   count(cust) as num_purchasers_year,
   sum(avg_num_orders) as num_orders_year,
   avg(avg_num_orders) as avg_num_order_year,
   sum(avg_sales) as avg_sales_year,
   sum(avg_total_return) as avg_return_year,
   avg_sales_year/num_orders_year as AOV,
   avg_sales_year/num_purchasers_year as ACS,
   stddev(avg_sales) as sales_stddev
from b
where avg_num_orders > .25
group by estimated_frequency
order by  estimated_frequency;

我想编写以下代码(这是不起作用的，我提供了伪代码)。我没有创建过程的权限。

Create table temp1
for i in 1..100 loop
insert into temp1 the result of QUERY1
end loop

然后

select estimated_frequency,
       avg(acs),
       avg(sales_stddev)
from temp1
group by estimated_frequency 

本质上，我想运行query1 100次，并将结果存储在一个名为temp1的表中，然后在完成所有操作之后，在temp1上计算一些平均值。

谢谢你的帮助

Answer 1

我会把这作为一个评论，但没有足够的代表。

我看到的唯一选项是在Netezza之外执行这个操作，并在批处理文件/shell脚本/Python脚本/.

我尝试了下面的方法，但是注意到这不起作用，因为随机数只生成一次，然后再被重用，所以您可以得到100个相同的样本。

-- Test view which gives some random data from an existing table.
create view my_view as 
    select 
        m.*
    from my_table t
    join (
        select (floor(random()*10)+1)::integer rand_id -- assuming I have ids from 1 to 10
    ) x on x.rand_id = t.id;

create table results (id integer, data double precision);

insert into results
    select v.*
    from my_view v
    cross join table(generate_series(1,100));

Generate_series是一个用户定义的表函数，您可以从Enzee社区网站中获得它。