Netezza不支持子查询和类似查询...有任何解决方法? [英] Netezza not supporting sub query and similar... any workaround?

问题描述

我敢肯定，对于大多数人来说，这将是一个非常简单的问题，但这使我发疯…… 我有一个这样的表(简化):

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 |

我需要每天提取当天进行购买的客户数量，以及当前一天之前30天内至少进行购买的客户数量.

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返回一个错误，指出不支持子查询，我应该考虑重写查询.但是如何?！?！? 我尝试使用case when语句，但是没有用.实际上，以下内容:

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

未返回任何错误，但是MAU和DAU列相同(这是错误的). 有人可以帮我吗?非常感谢

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

推荐答案

我终于明白了:)对于所有感兴趣的人，这是我解决它的方法:

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;

希望这会有所帮助.

这篇关于Netezza不支持子查询和类似查询...有任何解决方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！