当累积和达到一个标记时获取日期

I have a table in the following format:

我有以下格式的表格:

   APP_iD|  Date       |   Impressions
    113  2015-01-01     10
    113  2015-01-02     5
    113  2015-01-03     50
    113  2015-01-04     35
    113  2015-01-05     30
    113  2015-01-06     75

Now, I need to know the date when cumulative SUM of those impressions crossed 65/100/150 and so on. I tried using CASE WHEN statement:

现在，我需要知道这些印象的累计总和超过65/100/150的日期。我尝试用CASE WHEN语句:

CASE WHEN SUM(impressions) >100
     THEN date

but it doesn't sum the data across the column. It just does checks against the individual row.

但它并没有对整个列的数据进行求和。它只对单个行进行检查。

My final result should look like:

我的最终结果应该是:

APP_iD  | Date_65   | Date_100   | Date_150
113       2015-01-03  2015-01-04   2015-01-06

Does anyone know how to do this? Is this even possible?

有人知道怎么做吗?这是可能吗?

3 个解决方案

#1

with c as (
    select
        app_id, date,
        sum(impressions) over (partition by app_id order by date) as c
    from t
)
select app_id, s65.date, s100.date, s150.date
from
    (
        select distinct on (app_id) app_id, date
        from c
        where c >= 65 and c < 100
        order by app_id, date
    ) s65
    left join
    (
        select distinct on (app_id) app_id, date
        from c
        where c >= 100 and c <150
        order by app_id, date
    ) s100 using (app_id)
    left  join
    (
        select distinct on (app_id) app_id, date
        from c
        where c >= 150
        order by app_id, date
    ) s150 using (app_id) 
;
 app_id |    date    |    date    |    date    
--------+------------+------------+------------
    113 | 2015-01-03 | 2015-01-04 | 2015-01-06

Without the pivot:

没有主:

select distinct on (app_id, break) app_id, break, date
from (
    select *,
        case
            when c < 100 then 65
            when c < 150 then 100
            else 150
        end as break
    from (
        select
            app_id, date,
            sum(impressions) over (partition by app_id order by date) as c
        from t
    ) t
    where c >= 65
) t
order by app_id, break, date
;
 app_id | break |    date    
--------+-------+------------
    113 |    65 | 2015-01-03
    113 |   100 | 2015-01-04
    113 |   150 | 2015-01-06

#2

Use sum() over() to get the running sum and check for the required values with a case expression. Finally aggregate the results to get one row per each app_id.

使用sum() /()获取运行和并使用case表达式检查所需的值。最后，将结果聚合为每个app_id的一行。

select app_id,max(dt_65),max(dt_100),max(dt_150) 
from (
select app_id
,case when sum(impressions) over(partition by app_id order by dt) between 65 and 99 then dt end dt_65
,case when sum(impressions) over(partition by app_id order by dt) between 100 and 149 then dt end dt_100
,case when sum(impressions) over(partition by app_id order by dt) >= 150 then dt end dt_150
from t) x
group by app_id

#3

You can try this for desired result.

您可以尝试为期望的结果。

    with t as (select app_id, date, sum(Impressions) 
     over (partition by app_id order by date) AS s from tbl)
        select app_id, 
                min(date_65) AS date_65 , 
                min(date_100) AS date_100, 
                min(date_150) AS date_150
                -- more columns to observe other sum of Impressions
                from
                    (select app_id, 
                    CASE WHEN (s >= 65 and s < 100) THEN date END AS date_65,
                    CASE WHEN (s >= 100 and s < 150) THEN date END AS date_100,
                    CASE WHEN (s >= 150 ) THEN date END AS date_150
                    -- more cases to observe other sum of Impressions    
                    from t) q 
                    group by q.app_id

if you want to observe more sum of Impressions, just add more conditions

如果你想观察更多的印象，只要添加更多的条件