用户多久更改一次设备，查询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   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

select id, 
       count(distinct device) * 1.0 / count(distinct year * 100 + month) as prom_dif
from table_1
where device is not null and device <> ''
group by id

Answer 2

以下是dea：

对于每一行，查看上一行并比较设备(使用LAG).

• Use相邻月份号轻松处理月份(year * 12 + month) )，即可以轻松计算每个ID的月数、月数和更改数。

• 将更改的数量除以月数。

查询：

select
  id,
  max(month_num) - min(month_num) + 1 as months,
  sum(change_mark) as changes,
  sum(change_mark) * 1.0 / (max(month_num) - min(month_num) + 1) as average
from
(
  select 
    id,
    year * 12 + month as month_num,
    case when device <> lag(device) over (partition by id order by year, month)
      then 1 else 0 end as change_mark
  from table_1
) changes_marked
group by id
order by id;

演示：https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=36112bfa58a2f1afd042a1a4d5564e2d

这考虑到用户可以从设备A切换到B，然后返回到A，然后返回到B，因此我们必须计算三个更改，尽管它只是两个不同的设备。

如果表中缺少了几个月，计算中也会考虑到这一点。这意味着，如果您只有一月和十二月以及一个更改，那么查询将显示0.083 (12个月中有一个更改)。

(顺便说一句，ID在这里是一个不好的名称，因为尽管它的名称不是唯一地标识表中的一行，但它似乎是一个用户。所以最好调用这个user_num，或者--如果存在用户表--调用user_id。)