SQL-Query get best incidents by solution_time for each month

[Christopher90]

I have the following SQL-Query in Metabase:

SELECT
date_trunc('month', "resolvedAt") AS "resolvedAt",
(CAST(avg("solution_time") AS float) / CASE WHEN 3600.0 = 0 THEN NULL ELSE 3600.0 END) AS "Average Resolution Time",
12 AS "Minium",
8 AS "Expected"
FROM "custom_Incident"
RIGHT JOIN "ims_Incident" ON "incident" = "ims_Incident"."id"
WHERE {{resolveAt}}
AND {{assigned_group_name}}
AND {{service}}
AND {{incident_type}}
AND "statusDetail" = 'Closed'
AND "ims_Incident"."uid" NOT IN
(SELECT "uid"
FROM "ignore_Incident")
AND "ims_Incident"."uid" IN
(SELECT "incident_id"
FROM "custom_Incident"
WHERE "custom_Incident"."incident_id" in
(SELECT "uid"
FROM "ims_Incident"
WHERE {{resolveAt}}
AND {{assigned_group_name}}
AND {{service}}
AND {{incident_type}}
AND "statusDetail" = 'Closed'
AND "ims_Incident"."uid" NOT IN
(SELECT "uid"
FROM "ignore_Incident"))
ORDER BY "solution_time"
LIMIT {{take_into_account}})
GROUP BY date_trunc('month', "ims_Incident"."resolvedAt")


And I want to get the best ... let`s say 100 ... Incidents orderd by "solution_time" PER MONTH. So now I get the best with {{take_into_account}} (100) for the hole timeperiod of {{resolveAt}} but I want to split the {{resolveAt}} timeperiod (maybe 3 month) and for each of the month I want to get the best 100 Incidents orderd by "solution_time". So with 3 month there would be 300 incidents with {{take_into_account}} = 100 there would be 100 for each month.


How can I do that?

 

[akretschmer]

you can use row_number() and partition by month and over that a where-condition on that row_number lower than 100.
This is a german speaking group, can we switch to german?

 

[Christopher90]

Ja, wir können auch auf Deutsch reden. Wie würdest du die von dir genannten Punkte in die Query einbauen?

 

[akretschmer]

ich kenne Deine Tabelle(n) nicht und werde jetzt nicht versuchen, das reverse zu erforschen. Hier aber ein Beispiel für row_number():

PostgreSQL ROW_NUMBER() Explained with Practical Examples

In this tutorial, you will learn how to use the PostgreSQL ROW_NUMBER function to assign a unique integer value to each row in a result set.

www.postgresqltutorial.com

 

[Christopher90]

Ich habe jetzt diese Query erstellt:

SELECT
date_trunc('month', "resolvedAt") AS "resolvedAt",
"solution_time" AS "SolutionTime"
FROM (
SELECT
"resolvedAt",
"solution_time",
ROW_NUMBER() OVER (PARTITION BY date_trunc('month', "resolvedAt") ORDER BY "solution_time") AS RowNum
FROM (
SELECT
"resolvedAt",
"solution_time"
FROM "custom_Incident"
RIGHT JOIN "ims_Incident" ON "incident" = "ims_Incident"."id"
WHERE {{resolveAt}}
AND {{assigned_group_name}}
AND {{service}}
AND {{incident_type}}
AND "statusDetail" = 'Closed'
AND "ims_Incident"."uid" NOT IN
(SELECT "uid" FROM "ignore_Incident")
) AS Subquery
) AS RankedIncidents
WHERE RowNum <= {{take_into_account}}
ORDER BY "resolvedAt", "SolutionTime";

Wäre das vom Aufbau her richtig? Das jeweils die besten {{take_into_account}} = z. B.100 Incidents eines Monats ausgegeben werden oder hat die Query einen logischen Fehler?

 

[dabadepdu]

Wäre das vom Aufbau her richtig?

Ehrlich gesagt, das ist nur eine Query, die sagt nicht viel über Deine Daten und ebenso wenig über das Modell. Was möchtest Du haben? Einen Syntaxscan? Was passiert, wenn Du Deine Abfrage startest? Traust Du Dich nicht? Kommt keine Antwort? .. ein Fehler?
Ich kenne Metabase nicht, aber ich gehe mal davon aus, dass die Variablen irgendwelche Suchkriterien sind.
Normalerweise nennt man ein kleines Beispiel, Table Create, ein paar Inserts dazu und jeder Helfer kennt genau die Ausgangslage.

Vielleicht ist row_numer okay, die Over Clause scheint mir zu mager. Es gibt jedes Jahr die gleichen Monate und die willst Du nicht vermischen.
Dann gibt es auch noch andere Window Functions, die vielleicht besser passen? Rank() oder DenseRank()..

Ganz generell halte ich die ganzen Where In für suboptimal, kann man machen, ist wahrscheinlich nicht sehr performant. Where In kann man immer in einen Join umwandeln und sollte man auch tun, wenn die Mengen, die damit verarbeitet werden groß sind. Where In ist für mich eher etwas, um eine Handvoll Klassifizierungen aus 3 Dutzend auszuwählen.

 

[castorp]

Where In kann man immer in einen Join umwandeln

Nein, kann man nicht. Postgres 15

 

[dabadepdu]

Guter Hinweis! Das muss man beachten.
Ich würde allerdings bei meiner Aussage bleiben. Man kann ja ziemlich einfach dafür sorgen, dass der Join eindeutig wird.