代码之家 › 专栏 › 技术社区 › watseob

如何使用SQL转换此原始表?

etl amazon-redshift sql

-5

watseob · 技术社区 · 7 年前

我需要漏斗分析。如何使用SQL转换原始数据?

我的方法是

case when(get time diff over partitioned uid ordered time)>1分钟( 这是一个阈值)。然后是1。else 0结束别名开始 .
和groupby 开始获取cumsum。
和groupby cumsum concat事件

我认为这个过程需要很长时间,以及如何获取begin\u date和end\u date。我不知道如何准确地查询sql语句。

1 回复 | 直到 7 年前

Adrian Maxwell 7 年前

这里有一个方法,演示 SQL Fiddle

CREATE TABLE Table1
    ("id" int, "datetime" time, "name" varchar(1))
;

INSERT INTO Table1
    ("id", "datetime", "name")
VALUES
    (1, '11:10:01', 'A'),
    (1, '11:10:02', 'A'),
    (1, '11:10:05', 'B'),
    (1, '11:12:02', 'A'),
    (1, '11:12:10', 'A'),
    (2, '11:13:02', 'B'),
    (2, '11:13:06', 'A'),
    (1, '11:14:01', 'A'),
    (1, '11:14:02', 'B'),
    (1, '11:14:05', 'A')
;

查询1 :

with cte as (
  select
     id, datetime, name
  , row_number() over(partition by id, date_trunc('minute',datetime) order by datetime) rn
  , lead(name) over(partition by id, date_trunc('minute',datetime) order by datetime) lead_name
  from table1
  )
, cte2 as (
    select
    id, date_trunc('minute',datetime) t, concat(name,lead_name) pair
    from cte
    where lead_name IS NOT NULL
   )
select
       id
     , t
     , count(case when pair = 'AA' then 1 end) as AA
     , count(case when pair = 'AB' then 1 end) as AB
     , count(case when pair = 'BA' then 1 end) as BA
     , count(case when pair = 'BB' then 1 end) as BB
from cte2
group by
       id
     , t
order by
       id
     , t
;

Results :

| id |                                                t | aa | ab | ba | bb |
|----|--------------------------------------------------|----|----|----|----|
|  1 | 0 years 0 mons 0 days 11 hours 10 mins 0.00 secs |  1 |  1 |  0 |  0 |
|  1 | 0 years 0 mons 0 days 11 hours 12 mins 0.00 secs |  1 |  0 |  0 |  0 |
|  1 | 0 years 0 mons 0 days 11 hours 14 mins 0.00 secs |  0 |  1 |  1 |  0 |
|  2 | 0 years 0 mons 0 days 11 hours 13 mins 0.00 secs |  0 |  0 |  1 |  0 |

查询v2 :

with cte as (
  select
     id, datetime, name
  , row_number() over(partition by id order by datetime) rn
  , lead(name) over(partition by id order by datetime) lead_name
  from table1
  )
, cte2 as (
    select
    id, date_trunc('minute',datetime) t, concat(name,lead_name) pair
    from cte
    where lead_name IS NOT NULL
   )
select
       id
     , t
     , count(case when pair = 'AA' then 1 end) as AA
     , count(case when pair = 'AB' then 1 end) as AB
     , count(case when pair = 'BA' then 1 end) as BA
     , count(case when pair = 'BB' then 1 end) as BB
from cte2
group by
       id
     , t
order by
       id
     , t
;

Results v2 :

| id |                                                t | aa | ab | ba | bb |
|----|--------------------------------------------------|----|----|----|----|
|  1 | 0 years 0 mons 0 days 11 hours 10 mins 0.00 secs |  1 |  1 |  1 |  0 |
|  1 | 0 years 0 mons 0 days 11 hours 12 mins 0.00 secs |  2 |  0 |  0 |  0 |
|  1 | 0 years 0 mons 0 days 11 hours 14 mins 0.00 secs |  0 |  1 |  1 |  0 |
|  2 | 0 years 0 mons 0 days 11 hours 13 mins 0.00 secs |  0 |  0 |  1 |  0 |