SQL Server, eine beliebige Folge von Werten finden

Question

Nehmen wir an, wir haben eine Tabelle Wartung

Customer LastLogin ActionType
1        12/1/2007 2
1        12/2/2007 2
etc.

Wir wollen eine Liste aller Kunden, die zu irgendeinem Zeitpunkt in einem bestimmten Jahr eine oder mehrere ununterbrochene Sequenzen von 14 Tagen Dauer mit dem Aktionstyp 2 hatten.

Ich kann dies natürlich leicht mit Code tun, und es geht sogar ziemlich schnell über kleine Mengen. Gibt es eine nicht-Cursor Weg, um es in SQL zu tun?

Answer 1

Dadurch werden alle Kunden ausgewählt, die mindestens zwei aufeinanderfolgende Aktionen der gleichen Art haben.

WITH    rows AS 
        (
        SELECT  customer, action,
                ROW_NUMBER() OVER (PARTITION BY customer ORDER BY lastlogin) AS rn
        FROM    mytable
        )
SELECT  DISTINCT customer
FROM    rows rp
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    rows rl
        WHERE   rl.customer = rp.customer
                AND rl.rn = rp.rn + 1
                AND rl.action = rp.action
        )

Hier ist die effizientere Abfrage für eine einfache Aktion 2 :

WITH    rows AS 
        (
        SELECT  customer, ROW_NUMBER() OVER (PARTITION BY customer ORDER BY lastlogin) AS rn
        FROM    mytable
        WHERE   action = 2
        )
SELECT  DISTINCT customer
FROM    rows rp
WHERE   EXISTS
        (
        SELECT  NULL
        FROM    rows rl
        WHERE   rl.customer = rp.customer
                AND rl.rn = rp.rn + 1
        )

Update 2:

Um ununterbrochene Bereiche auszuwählen:

WITH    rows AS 
        (
        SELECT  customer, action, lastlogin
                ROW_NUMBER() OVER (PARTITION BY customer ORDER BY lastlogin) AS rn
                ROW_NUMBER() OVER (PARTITION BY customer, action ORDER BY lastlogin) AS series
        FROM    mytable
        )
SELECT  DISTINCT customer
FROM    (
        SELECT  customer
        FROM    rows rp
        WHERE   action
        GROUP BY
                customer, actioncode, series - rn
        HAVING
                DETEDIFF(day, MIN(lastlogin), MAX(lastlogin)) >= 14
        ) q

Diese Abfrage berechnet zwei Reihen: eine liefert zusammenhängende ORDER BY lastlogin , die zweite Teilung durch action zusätzlich:

action  logindate rn  series diff = rn - series
1       Jan 01    1   1      0
1       Jan 02    2   2      0
2       Jan 03    3   1      2
2       Jan 04    4   2      2
1       Jan 05    5   3      2
1       Jan 06    6   4      2

Solange die Differenz zwischen den beiden Schemata gleich ist, sind die Reihen ununterbrochen. Jede Unterbrechung unterbricht die Reihe.

Dies bedeutet, dass die Kombination von ( action, diff ) definiert die ununterbrochenen Gruppen.

Wir können gruppieren nach action, diff finden MAX y MIN innerhalb der Gruppen und filtern nach ihnen.

Wenn Sie Folgendes auswählen müssen 14 Zeilen statt 14 aufeinanderfolgende Tage, nur Filter auf COUNT(*) anstelle der DATEDIFF .

Answer 2

EDIT: Das hätte auch für die ursprüngliche Frage nach zwei in einer Reihe funktioniert. 14 in einer Reihe ist eine andere Antwort

Zuerst brauchen Sie eine Sequenz, also verwenden Sie ROWNUMBER

Sie können einen SELF-JOIN Maintenace an sich selbst durchführen, indem Sie ROWNUMBER = ROWNUMBER + 1

Wenn Sie zwei aufeinanderfolgende Zeilen mit der gleichen Kundennummer und beide Zeilen mit dem Aktionstyp "2" haben, erhalten Sie die Liste KUNDE als Antwort.

Versuchen Sie dies

WITH Maintenance AS
(
SELECT 1 as Customer, CONVERT (DateTime, '1/1/2008') DateTimeStamp, 1 ActionType
UNION
SELECT 1, '3/1/2009', 1
UNION
SELECT 1, '3/1/2006', 2
UNION
SELECT 2, '3/1/2009', 1
UNION
SELECT 2, '3/1/2006', 2
)
,RowNumberMaintenance AS
(SELECT  ROW_NUMBER () OVER (ORDER BY Customer, DateTimeStamp)  AS RowNumber, *
FROM Maintenance)
SELECT m1.Customer
From RowNumberMaintenance M1
    INNER JOIN RowNumberMaintenance M2
        ON M1.Customer = M2.Customer
        AND M1.RowNumber = M2.RowNumber + 1
WHERE 1=1
        AND M1.ActionType <> 2
        AND M2.ActionType <> 2

Answer 3

Verwendung:

WITH dates AS (
  SELECT CAST('2007-01-01' AS DATETIME) 'date'
  UNION ALL
   SELECT DATEADD(dd, 1, t.date) 
     FROM dates t
    WHERE DATEADD(dd, 1, t.date) <= GETDATE())
   SELECT m.customer, 
          m.actiontype
     FROM dates d
LEFT JOIN MAINTENANCE m ON m.last_login = d.date
    WHERE m.last_login IS NULL

Answer 4

select customerID, count(customerID)
from maintenance
where actiontype = 2
group by customerID
having count(customerID) >= 1

Answer 5

Ich gehe davon aus, dass Sie mit einer Sequenz zwei oder mehr Zeilen mit aufeinanderfolgenden Datumswerten meinen, ohne dass dazwischen weitere Zeilen für denselben Benutzer mit einem anderen Aktionstyp liegen. Wenn das der Fall ist, sollten Sie damit das bekommen, was Sie suchen:

SELECT DISTINCT
     T1.customer
FROM
     Maintenance T1
INNER JOIN Maintenance T2 ON
     T2.customer = T1.customer AND
     T2.action_type = 2 AND
     T2.last_login > T1.last_login
LEFT OUTER JOIN Maintenance T3 ON
     T3.customer = T1.customer AND
     T3.last_login > T1.last_login AND
     T3.last_login < T2.last_login AND
     T3.action_type <> 2
WHERE
     T1.actiontype = 2 AND
     T3.customer IS NULL

Das SQL macht genau das, was ich oben gesagt habe - es findet eine Zeile (T1) mit einer weiteren Zeile danach (T2), beide mit action_type = 2, wo es keine Zeile dazwischen (T3) mit einem anderen Aktionstyp gibt. T3.customer IS NULL prüft auf NULL, denn wenn die Spalte NULL ist (ich gehe davon aus, dass es sich um eine NOT NULL-Spalte handelt), dann bedeutet dies, dass der LEFT OUTER JOIN keine Zeile gefunden haben darf, die den Kriterien entspricht.