最后一个值与最大值（日期）

Question

我想找出使用​​ ios 应用程序和使用 android 应用程序的应用程序用户总数，但我对所有时间都不感兴趣，我只想知道最后一个值。 所以，我知道我将使用 lastvalue 和 max(date) 但在运行代码时遇到一些错误。 我将在这里分享代码：

SELECT last_value(DEVICE_TYPE) OVER (PARTITION BY CREATED_AT ORDER BY CREATED_AT) AS Device, date(max(CREATED_AT)) FROM USER_DEVICES
GROUP BY DEVICE_TYPE, CREATED_AT

Answer 1

有趣..

我用一些虚拟数据创建了一个 CTE，以查看代码是否生成错误等：

WITH USER_DEVICES AS (
    SELECT * FROM VALUES
    ('Apple', '2022-01-01'::date),
    ('Apple', '2022-01-02'::date),
    ('Andriod', '2022-02-01'::date),
    ('Andriod', '2022-02-02'::date)
    t(DEVICE_TYPE, CREATED_AT)
)

SELECT 
    last_value(DEVICE_TYPE) OVER (PARTITION BY CREATED_AT ORDER BY CREATED_AT) AS Device, 
    date(max(CREATED_AT)) 
FROM USER_DEVICES
GROUP BY DEVICE_TYPE, CREATED_AT;

它运行良好，并给出：

DEVICE	DATE(MAX(CREATED_AT))
Apple	2022-01-01
Apple	2022-01-02
Andriod	2022-02-01
Andriod	2022-02-02

所以 date(max(CREATED_AT)) 正在查找最后一个 create_at，然后再次将其转换为日期。其中最后一位是不需要的，因此它可能只是 max(CREATED_AT)

但您的 last_value 是按对这些分区进行排序的相同值进行分区的，因此您只会得到“所有结果”

因此，考虑到所有这些，让我们重新阅读您的问题，并尝试找到真正的意图并猜测代码......

我想了解在 ios 上使用该应用程序以及在 Android 上使用该应用程序的用户总数

这让我觉得您想要每天进行分组，并进行一些计数..

WITH USER_DEVICES AS (
    SELECT * FROM VALUES
    ('Apple', '2022-01-01'::date),
    ('Apple', '2022-01-01'::date),
    ('Andriod', '2022-02-01'::date),
    ('Andriod', '2022-02-01'::date)
    t(DEVICE_TYPE, CREATED_AT)
)
SELECT
    CREATED_AT,
    count_if(DEVICE_TYPE = 'Apple') as apple_count,
    count_if(DEVICE_TYPE = 'Andriod') as andriod_count
FROM USER_DEVICES
GROUP BY CREATED_AT;

这给出了：

CREATED_AT	APPLE_COUNT	ANDRIOD_COUNT
2022-01-01	2	0
2022-02-01	0	2

但我对所有时间都不感兴趣，我只想知道最后一个值

所以我假设你的意思是你只想要最近一天的结果。

这可以通过 QUALIFY

SELECT
    CREATED_AT,
    count_if(DEVICE_TYPE = 'Apple') as apple_count,
    count_if(DEVICE_TYPE = 'Andriod') as andriod_count
FROM USER_DEVICES
GROUP BY CREATED_AT
QUALIFY ROW_NUMBER() OVER (ORDER BY CREATED_AT DESC) = 1;

CREATED_AT	APPLE_COUNT	ANDRIOD_COUNT
2022-02-01	0	2来完成

Answer 2

如果我理解正确的话，对于每个用户，您希望在聚合每个设备类型的用户之前包含与该用户最近的应用程序活动日期关联的设备类型。

with cte as 
(select *, row_number() over(partition by user_id order by created_at desc) as rn
 from user_devices)
 
 
select device_type, count(*) as counts
from cte
where rn=1
group by device_type;