postgresql-子查询，用于在特定实例之后查找随机实例。不能作为主查询的一部分 [英] postgresql - Subquery to look for random instance following a specific instance. Not working as part of master query
问题描述
仅需澄清一下，以此类推，这是我上一篇文章的后续内容：
Just to clarify for moderation etc, this is a follow up from my previous post below:
该问题已解决，但是我现在需要将该答案作为较大查询的一部分，并且由于难以使它起作用而需要询问另一个问题。
That question was resolved however I now need to include that answer as part of a larger query and need to ask another question due to difficulties getting this to work.
上一个查询使我能够在一条数据列表中提取绿线，如下图所示：
The previous query enabled me to extract the green line, as highlighted by the image below, in a single list of data:
https://postimg.org/image/va7n814s9/
我现在需要遍历多个指定列表的同时循环此子查询，直到我的主查询完成一些特定计数为止。遗憾的是，当尝试实现此目的时，主查询仅使该子查询执行一次，而不是遍历所有列表。我尝试以多种不同的方式对此进行编码，包括CTE和派生表样式，但是无论我如何构造它，结果都是相同的。这使我相信我可能需要对子查询进行稍微不同的编码，以便主查询知道继续重复子查询条件。
I now need to loop this subquery whilst working through a number of specified lists until my master query has completed a few specific counts. Regrettably when trying to achieve this the master query kept executing this subquery only once rather than looping through all the lists. I tried to code this in a number of different ways including CTE and derived tables styles, however no matter how I structure it the results are the same. This leads me to believe I might need to code the subquery slightly differently for the master query to know to keep repeating the subquery condition.
为清楚起见，这是我拥有的最新代码的示例，因此您可以确切地知道我要去哪里了（已编辑以包括代码并删除代码链接，抱歉，在此发布的内容相对较新）：
For clarity here is an example of the latest code I have so you can see exactly where I'm going wrong (edited to include code and remove link to code, my apologies relatively new to posting here):
SELECT table_1.agent
,COUNT(DISTINCT CASE WHEN table2.queue=1 AND table2.status=0 THEN table2.id ELSE NULL END)AS count_1
,COUNT(DISTINCT CASE WHEN table2.queue=2 AND table2.status=0 THEN table2.id ELSE NULL END)AS count_2
,COUNT(DISTINCT CASE WHEN table2.queue=3 AND table2.status=0 THEN table2.id ELSE NULL END)AS count_3
,COUNT(DISTINCT CASE WHEN table2.queue=4 AND table2.status=0 THEN table2.id ELSE NULL END)AS count_4
FROM historic_table table_1 JOIN current_table table_2 ON table_1.reviewid=table2.id
JOIN (SELECT table_1a.*
FROM historic table_1a
WHERE table_1a.id >(SELECT MAX(table_1b.id)
FROM historic table_1b
WHERE table_1b.queue=42)
ORDER BY table_1a.id FETCH FIRST 1 ROW ONLY) next_instance ON table_1.id=next_instance.id
WHERE table_1.currenttimestamp>=(current_date-7)
GROUP BY table_1.agent
很多再次感谢您的帮助，非常感谢！
Many thanks in advance again for your help, it's very much appreciated!
推荐答案
好的，因此，在经过反复试验之后，我设法编写了以下代码以提供所需的结果集：
OK so after a lot of trial and error I managed to write the following code to give me the results set I wanted:
WITH alpha AS
(SELECT table1a.urid, (SELECT MIN(table1b.classifier) FROM historic_table table1b WHERE table1b.urid=table1a.urid) AS min_agent
FROM historic_table table1a
WHERE table1a.queue=42
AND table1a.tstamp>=(current_date-7))
,stats AS
(SELECT a.min_agent AS name
,COUNT(DISTINCT CASE WHEN table2.queue=1 AND table2.status=0 THEN table2.id ELSE NULL END)AS count_1
,COUNT(DISTINCT CASE WHEN table2.queue=2 AND table2.status=0 THEN table2.id ELSE NULL END)AS count_2
,COUNT(DISTINCT CASE WHEN table2.queue=3 AND table2.status=0 THEN table2.id ELSE NULL END)AS count_3
,COUNT(DISTINCT CASE WHEN table2.queue=4 AND table2.status=0 THEN table2.id ELSE NULL END)AS count_4
FROM current_table table2 JOIN alpha a ON table2.id=a.urid
GROUP BY a.min_agent)
SELECT s.*
,s.count_1+s.count_2+s.count_3+s.count_4 AS total_count
FROM stats s
GROUP BY s.name
,s.count_1
,s.count_2
,s.count_3
,s.count_4
HAVING (s.count_1+s.count_2+s.count_3+s.count_4)>0
ORDER BY total_count DESC
这篇关于postgresql-子查询，用于在特定实例之后查找随机实例。不能作为主查询的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
