在PostgreSQL中按查询分组 [英] Group by query in postgresql

问题描述

我有2张桌子

表1：

id | user_id  | pattern
1  |   3      | ^1212.*
2  |   3      | ^192.*
3  |   4      | ^20.*

表2：

id |pattern | start_date          | comment
1  |^1212.* | 2014-03-22 20:10:13 | India-Gujarat
2  |^1212.* | 2014-03-24 20:10:13 | India -Maharastra
3  |^1212.* | 2014-03-25 20:10:13 | India -uttar pradesh
4  |^1212.* | 2014-03-27 20:10:13 | India -Madhya pradesh
5  |^1212.* | 2014-03-29 20:10:13 | India -Rajasthan
6  |^192.* | 2014-03-22 20:10:13  | Africa
7  |^20.* | 2014-03-22 20:10:13   | Indonesia- first
8  |^20.* | 2014-03-26 20:10:13   | indonesia -second
9  |^1212.* | 2014-03-22 20:10:13 | India- kerala
10 |^13.* | 2014-03-22 20:10:13   | Usa
11 |^13.* | 2014-03-22 20:12:13   | usa
12 |^14.* | 2014-03-22 20:10:13   | U.k

必需的输出：

id | pattern | start_date
8  |^20.* | 2014-03-26 20:10:13 | Indonesia-first
10 | ^13.*  | 2014-03-22 20:12:13 | USA
12 | ^14.*  | 2014-03-22 20:10:13 | U.k

输出要求：

1. 基于user_id = {当前用户}的表1中不存在模式
   ，在这里我们可以看到user_id有1212,92,20但它没有20表1中的，13,14模式


2. 表2中显示的模式及其注释以及start_date必须比当前时间最接近


3. 模式必须是不同的

我尝试使用php代码执行3次执行查询，并且需要很长时间

i have tried using php code using 3 times execute query and its take long time

1. 从表2中按模式分组选择不同的（模式）


2. 从表1中选择*，其中user_id = login_id


3. foreach循环在php中（从表2中选择*，其中id不在（query2的输出）中，并且模式类似于（'123'））

所以可以在postgresql中使用单个查询

so is it possible using single query in postgresql

推荐答案

这应该可以解决问题：

SELECT DISTINCT a.pattern
FROM table2 a
LEFT OUTER JOIN table1 b ON a.pattern = b.pattern AND b.user_id = {current_user}
WHERE b.id IS NULL

根据表2的模式并且仅当它属于当前用户时，才将表2中的所有行与表1中的所有行连接起来。然后，您只能从a中选择那些不能加入的对象。

You join all rows from table 2 with all the rows from table 1, based on their pattern and only if it belongs to the current user. Then you select only those from a, which could not be joined.

根据您的评论，我也不是100％知道你的意思。一种方法是在上面的查询中添加 DISTINCT 。

Based on your comment, again I am not 100% sure what you mean. One way would be adding DISTINCT to the query above.

另一种方法是

SELECT DISTINCT pattern FROM table2
EXCEPT
SELECT pattern FROM table1 WHERE user_id = {current user}

更新2

根据您想要的信息，表2的最新行，此查询可能会帮助您：

Update 2

Based on the information that you want the additional information from the most recent row of table 2, this query might help you:

SELECT a.* FROM table2 a
INNER JOIN (
  SELECT pattern, MAX(start_date) AS max_start_date
  FROM table2
  GROUP BY pattern
) mostrecent
ON a.pattern = mostrecent.pattern AND a.start_date = mostrecent.max_start_date
LEFT OUTER JOIN table1 b ON a.pattern = b.pattern AND b.user_id = {current_user}
WHERE b.id IS NULL

上部从table2请求所有最近的行。下部（从 LEFT OUTER JOIN 开始）将其连接到表1，并（如以前一样）仅选择那些在table1中没有行的行。

The upper part requests all most-recent rows from table2. The lower part (beginning with LEFT OUTER JOIN) joins this to table 1 and (as before) selects only those rows, which have no row in table1.

另一个在Postgresql 8.4+中特别有效的解决方案将是

Another solution which works specifically in Postgresql 8.4+ would be

WITH mostrecent AS (
  SELECT *,
    ROW_NUMBER() OVER(PARTITION BY pattern ORDER BY start_date DESC) AS rank
  FROM table2
)
SELECT a.*
FROM mostrecent a
LEFT OUTER JOIN table1 b ON a.pattern = b.pattern AND b.user_id = {current_user}
WHERE b.id IS NULL AND a.rank = 1

这篇关于在PostgreSQL中按查询分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！