时间戳

首页 > 解决方案 > 如何在 SQL 中找到没有聚合函数的最小值？

sql - 如何在 SQL 中找到没有聚合函数的最小值？

问题描述

我在 Postgres 上有这个示例数据集

 updated_at     activated_at        name     gender   role    school  app_name    device_type
-------------+-----------------+----------+--------+-------+---------+---------+---------------
August 2         July 30            Ron        M       S        A         Y          android
August 1         July 30            Ron        M       S        A         Z          browser
July 30          July 30            Ron        M       S        A         Y          android
August 1         July 28            Ana        F       S        B         Y          android
August 1         July 28            Ana        F       S        B         Z          browser
July 28          July 28            Ana        F       S        B         Y          android

我想找出用户在激活后第一次显示（updated_at）的时间，并且属于哪个应用程序。

预期结果：

 updated_at     activated_at        name     gender   role    school  app_name    device_type
-------------+-----------------+----------+--------+-------+---------+---------+---------------
July 30          July 30            Ron        M       S        A         Y          android
July 28          July 28            Ana        F       S        B         Y          android

我试过这个 SQL：

SELECT min(ut.updated_at), u.activated_at, u.full_name, u.gender, r.name, s.name, ut.app_name, ut.device_type
FROM "public"."user_tokens" ut JOIN
     "public"."users" u
     ON ut.user_id = u.id JOIN
     "public"."user_roles" ur
     ON ut.user_id = ur.user_id JOIN
     "public"."roles" r
     ON ur.role_id = r.id JOIN
     "public"."schools" s
      ON ur.school_id = s.id
WHERE (NOT (ut.app_name) like 'G')
Group by u.activated_at, u.full_name, u.gender, r.name, s.name, ut.app_name, ut.device_type
Order by u.activated_at desc

但结果是这样的：

 updated_at     activated_at        name     gender   role    school  app_name    device_type
-------------+-----------------+----------+--------+-------+---------+---------+---------------
August 1         July 30            Ron        M       S        A         Z          browser
July 30          July 30            Ron        M       S        A         Y          android
August 1         July 28            Ana        F       S        B         Z          browser
July 28          July 28            Ana        F       S        B         Y          android

我试图从 group by 子句中排除app_nameand但它说device_typeERROR: column "ut.app_name" must appear in the GROUP BY clause or be used in an aggregate function

知道如何解决吗？任何输入将不胜感激。谢谢你。

标签： sqlpostgresqlgroup-bygreatest-n-per-groupmetabase

解决方案

我认为DISTINCT ON这可能是在 Postgres 上执行此操作的最佳方法：

SELECT DISTINCT ON (u.full_name)
    ut.updated_at,
    u.activated_at,
    u.full_name,
    u.gender,
    r.name,
    s.name,
    ut.app_name,
    ut.device_type
FROM "public"."user_tokens" ut
INNER JOIN "public"."users" u ON ut.user_id = u.id
INNER JOIN "public"."user_roles" ur ON ut.user_id = ur.user_id
INNER JOIN "public"."roles" r ON ur.role_id = r.id
INNER JOIN "public"."schools" s ON ur.school_id = s.id
WHERE NOT ut.app_name LIKE 'G'
ORDER BY
    u.full_name,
    ut.updated_at;

上面会为每个全名用户返回一条记录，对应较早的updated_at时间。

推荐阅读