SQL重新激活收入 [英] SQL Reactivation Revenue

问题描述

我正在寻找一个查询，该查询将从给定日期算起重新激活收入。当前，我有以下查询；

I'm looking for a query that will Sum Reactivation Revenue from a given date on. Currently I have the following query;

    SELECT advertisable, EXTRACT(YEAR from day), EXTRACT(MONTH from day), ROUND(SUM(cost)/1e6)  FROM adcube dac
WHERE advertisable_eid IN 

(SELECT advertisable FROM adcube dac 
GROUP BY advertisable HAVING SUM(cost)/1e6 > 100)

GROUP BY advertisable, EXTRACT(YEAR from day), EXTRACT(MONTH from day)
ORDER BY advertisable, EXTRACT(YEAR from day), EXTRACT(MONTH from day)

然后我将其导出到excel，并且支票帐户已经停止支出4个月，然后重新激活。然后，我跟踪新的重新激活月份的新收入。

From this i then export to excel and check accounts thay have stopped spending for 4 months and then reactivated. I then track the new revenue from the new reactivtion month.

是否可以通过SQL查询而无需Excel？

Is it possible to get an SQL query to do this without need of Excel?

谢谢

推荐答案

假设数据中实际存在四个月，则可以使用窗口函数来完成此操作。通过取两个 row_numbers（）之间的差，可以连续找到 N 个东西。就是这样：

Assuming the four months is actually present in the data, you can do this using window functions. You can find N things in a row by taking the difference between two row_numbers(). Here is the idea:

with t as (
      SELECT advertisable, EXTRACT(YEAR from day) as yy, EXTRACT(MONTH from day) as mon, 
             ROUND(SUM(cost)/1e6) as val
      FROM adcube dac
      WHERE advertisable_eid IN (SELECT advertisable
                                 FROM adcube dac 
                                 GROUP BY advertisable
                                 HAVING SUM(cost)/1e6 > 100
                                )
      GROUP BY advertisable, EXTRACT(YEAR from day), EXTRACT(MONTH from day)
     )
select advertisable, min(yy * 10000 + mon) as yyyymm
from (select t.*,
             (row_number() over (partition by advertisable order by yy, mon) -
              row_number() over (partition by advertisable, val order by yy, mon)
             ) as grp
      from t
     )
group by advertisable, grp, val
having count(*) >= 4 and val = 0;

这篇关于SQL重新激活收入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！