Netezza not supporting sub query and similar… any

问题:

I'm sure this will be a very simple question for most of you, but it is driving me crazy... I have a table like this (simplifying):

| customer_id | date | purchase amount |

I need to extract, for each day, the number of customers that made a purchase that day, and the number of customers that made at least a purchase in the 30 days previous to the current one.

I tried using a subquery like this:

select purch_date as date, count (distinct customer_id) as DAU, 
   count(distinct (select customer_id from table where purch_date<= date and purch_date>date-30)) as MAU
from table
group by purch_date

Netezza returns an error saying that subqueries are not supported, and that I should think to rewrite the query. But how?!?!? I tried using case when statement, but did not work. In fact, the following:

select purch_date as date, count (distinct customer_id) as DAU, 
   count(distinct case when (purch_date<= date and purch_date>date-30) then player_id else null end) as MAU
from table
group by purch_date

returned no errors, but the MAU and DAU columns are the same (which is wrong). Can anybody help me, please? thanks a lot

回答1:

I don't beleive netezza supports subqueries in the select line...move to the from statement

 select pur_date as date, count(distinct customer_id) as DAU
 from table
 group by purch_date

 select pur_date as date, count (distinct customer_ID) as MAU
 from table
 where purch_date<= date and purch_date>date-30
 group by purch_date

I hope thats right for MAU and DAU. join them to get the results combined:

 select a.date, a.dau, b.mau
 from
 (select pur_date as date, count(distinct customer_id) as DAU
 from table
 group by purch_date) a

 left join 
 (select pur_date as date, count (distinct customer_ID) as MAU
 from table
 where purch_date<= date and purch_date>date-30
 group by purch_date) b

 on b.date = a.date

回答2:

I got it finally :) For all interested, here is the way I solved it:

select a.date_dt, max(a.dau), count(distinct b.player_id)
from (select dt.cal_day_dt as date_dt, 
        count(distinct s.player_id) as dau
        FROM IA_PLAYER_SALES_HOURLY s
        join IA_DATES dt on dt.date_key = s.date_key
        group by dt.cal_day_dt
        order by dt.cal_day_dt
) a
join (
        select dt.cal_day_dt as date_dt, 
        s.player_id as player_id
        FROM IA_PLAYER_SALES_HOURLY s
        join IA_DATES dt on dt.date_key = s.date_key
        order by dt.cal_day_dt
) b on b.date_dt <= a.date_dt and b.date_dt > a.date_dt - 30
group by a.date_dt
order by a.date_dt;

Hope this is helpful.