DISTINCT ON查询瓦特/ ORDER BY列的最大值 [英] DISTINCT ON query w/ ORDER BY max value of a column

问题描述

我一直在负责将一个Rails应用程序从MySQL到Postgres的尽快解决，并跑进一个小问题。
活动记录查询：

current_user.profile_visits.limit（6）.order（created_at降序），其中（created_at＆GT;和visitor_id＆LT;＆GT;，2.months.ago，CURRENT_USER。 ID）.distinct

生成SQL：

  SELECT visitor_id，MAX（created_at）为created_at，独特的（visitor_id）*
从profile_visits
WHEREprofile_visits。social_user_id= 21
AND（created_at＆GT;2015年2月1日17：17：01.826897'AND visitor_id＆LT;＆GT; 21）
ORDER BY created_at DESC，ID降序
LIMIT 6
 

使用MySQL时，我pretty的信心，但我诚实新的Postgres。我想，这个查询失败的原因有多种。

1. 在我相信对需求的明显是第一次。
2. 在我不知道如何最大函数的结果下令
3. 我甚至可以使用的最大功能是这样？

该查询的高层次目标是返回用户的6最近的纵断面图。关于如何解决此问题的ActiveRecord查询任何指针（或它生成的SQL），将大大AP preciated。

解决方案

  

该查询的高层次目标是返回 6最近   用户的个人资料显示

这将是简单的。你不需要 MAX（）和 DISTINCT 此：

  SELECT *
从profile_visits
WHERE social_user_id = 21
和created_at＆GT; （NOW（） - 间隔'2个月）
与visitor_id＆LT;＆GT; 21  -  ??
ORDER BY created_at DESC NULLS LAST，ID DESC NULLS LAST
LIMIT 6;
 

我怀疑你的问题是不完整的。如果你想：
6个最新访客与他们最近一次访问的页面的
那么你需要一个子查询。你不能在一个查询的水平，既不符合 DISTINCT ON ，也没有与窗口函数此排序顺序：

  SELECT *
从  （
   SELECT DISTINCT ON（visitor_id）*
   从profile_visits
   WHERE social_user_id = 21
   和created_at＆GT; （NOW（） - 间隔'2个月）
   与visitor_id＆LT;＆GT; 21  -  ??
   ORDER BY visitor_id，created_at DESC NULLS LAST，ID DESC NULLS LAST
   ）子
ORDER BY created_at DESC NULLS LAST，ID DESC NULLS LAST
LIMIT 6;
 

该子查询子获取每个用户最新的访问（但不超过两个月，没有一定的游客 21 。 ORDER BY 必须具有相同的前导列作为 DISTINCT ON 。

您需要外部查询，以获得最新的6游人即可。
考虑事件序列：

• <一个href="http://stackoverflow.com/questions/156114/best-way-to-get-result-count-before-limit-was-applied-in-php-postgresql/8242764#8242764">Best方式得到的结果计数之前限制PHP / PostgreSQL的应用于

为什么 NULLS LAST ？可以肯定，你没有提供该表的定义。

• <一个href="http://stackoverflow.com/questions/9510509/postgresql-sort-by-datetime-asc-null-first/9511492#9511492">PostgreSQL排序日期时间递增，空第一？

I've been tasked with converting a Rails app from MySQL to Postgres asap and ran into a small issue.
The active record query:

current_user.profile_visits.limit(6).order("created_at DESC").where("created_at > ? AND visitor_id <> ?", 2.months.ago, current_user.id).distinct

Produces the SQL:

SELECT  visitor_id, MAX(created_at) as created_at, distinct on (visitor_id) *
FROM "profile_visits"
WHERE "profile_visits"."social_user_id" = 21
AND (created_at > '2015-02-01 17:17:01.826897' AND visitor_id <> 21)
ORDER BY created_at DESC, id DESC
LIMIT 6

I'm pretty confident when working with MySQL but I'm honestly new to Postgres. I think this query is failing for multiple reasons.

1. I believe the distinct on needs to be first.
2. I don't know how to order by the results of max function
3. Can I even use the max function like this?

The high level goal of this query is to return the 6 most recent profile views of a user. Any pointers on how to fix this ActiveRecord query (or it's resulting SQL) would be greatly appreciated.

解决方案

The high level goal of this query is to return the 6 most recent profile views of a user.

That would be simple. You don't need max() nor DISTINCT for this:

SELECT *
FROM   profile_visits
WHERE  social_user_id = 21
AND    created_at > (now() - interval '2 months')
AND    visitor_id <> 21  -- ??
ORDER  BY created_at DESC NULLS LAST, id DESC NULLS LAST
LIMIT  6;

I suspect your question is incomplete. If you want:
the 6 latest visitors with their latest visit to the page
then you need a subquery. You cannot get this sort order in one query level, neither with DISTINCT ON, nor with window functions:

SELECT *
FROM  (
   SELECT DISTINCT ON (visitor_id) *
   FROM   profile_visits
   WHERE  social_user_id = 21
   AND    created_at > (now() - interval '2 months')
   AND    visitor_id <> 21  -- ??
   ORDER  BY visitor_id, created_at DESC NULLS LAST, id DESC NULLS LAST
   ) sub
ORDER  BY created_at DESC NULLS LAST, id DESC NULLS LAST
LIMIT  6;

The subquery sub gets the latest visit per user (but not older than two months and not for a certain visitor21. ORDER BY must have the same leading columns as DISTINCT ON.

You need the outer query to get the 6 latest visitors then.
Consider the sequence of events:

• Best way to get result count before LIMIT was applied in PHP/PostgreSQL

Why NULLS LAST? To be sure, you did not provide the table definition.

• PostgreSQL sort by datetime asc, null first?

这篇关于DISTINCT ON查询瓦特/ ORDER BY列的最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！