确定更改设备的次数，SQL

Question

我有下表，我想用它创建新的变量。我想知道，“平均而言，你每月更换一次设备吗？”这是SQL中的table_1，Athenas

       id    date     month device      carrier
12-ac8147   2019       4    Sgx1         alca
12-ac8147   2019       5    Sgx1         alca
12-ac8147   2019       5    Sgx1         alca
12-ac8147   2019       6    Sgx1         alca
12-ac8147   2019       6    Sgx1         alca
12-ac8147   2019       6    iPhone8,1    telc
12-ac8147   2019       9    iPhone8,1    telc
12-ac8147   2019       9    iPhone8,1    telc
12-ac8147   2020       1    iPhone8,1    telc
12-ac8147   2020       10   iPhone11,2   telc
12-ac8147   2020       12   iPhone11,2   telc
12-ac8147   2020       12   iPhone11,2   service_m
12-ac8147   2020       12   iPhone11,2   service_m
12-ac8147   2020       12   iPhone11,2   service_m
12-ac8147   2021       1    iPhone11,2   service_m
12-ac8147   2021       12   iPhone11,5   service_m
12-ac8147   2021       12   iPhone11,5   movil_tel   

所以我的想法是，(在sql athenas aws amazon中)

with table as(
id, device, max(year*100+month) mas_n, min(year*100+month) min_n)
FROM table_1
where device is not null and device <>''
group by id, device
)

SELECT id, avg(max_n-min_n) prom_dif
FROM table
GROUP BY id

但这不管用，因为这几个月都算上了。

    id              prom_dif
12-ac8147            206 

Answer 1

;with datas as(
select '12-ac8147' id,'2019-03-29' date,'Sgx1' device,'alca' carrier
union select '12-ac8147','2019-04-04','Sgx1','alca'
union select '12-ac8147','2019-05-01','Sgx1','alca'
union select '12-ac8147','2019-05-13','Sgx1','alca'
union select '12-ac8147','2019-05-18','Sgx1','alca'
union select '12-ac8147','2019-05-19','iPhone8,1','telc'
union select '12-ac8147','2019-08-09','iPhone8,1','telc'
union select '12-ac8147','2019-08-16','iPhone8,1','telc'
union select '12-ac8147','2019-12-30','iPhone8,1','telc'
union select '12-ac8147','2020-09-24','iPhone11,2','telc'
union select '12-ac8147','2020-11-11','iPhone11,2','telc'
union select '12-ac8147','2020-11-17','iPhone11,2','service_m'
union select '12-ac8147','2020-11-19','iPhone11,2','service_m'
union select '12-ac8147','2020-11-25','iPhone11,2','service_m'
union select '12-ac8147','2020-12-03','iPhone11,2','service_m'
union select '12-ac8147','2021-12-03','iPhone11,5','service_m'
union select '12-ac8147','2021-12-17','iPhone11,5','movil_tel'   
),cte2 as(
select id,count(distinct device) no_of_device_changed,count(distinct carrier) no_of_carrier_changed from datas group by id)
,cte3 as(
select *,lag(carrier)over(partition by id order by date) prev_carrier from datas)
,cte4 as(
select * from cte3 where carrier <> coalesce(prev_carrier,'')
),cte5 as(
select *,lag(date) over(partition by id order by date) as prev_carrier_dt from cte4)
,cte6 as (select id,avg(date_diff('day',prev_carrier_dt,date)) avg_dt from cte5 group by id)
select a.id,a.avg_dt as avg_carrier_change,b.no_of_carrier_changed,b.no_of_device_changed from cte6 a
join cte2 b on a.id = b.id