Selektion inhaltsgleicher Warenkörbe

verwendetes Datenbanksystem: MySQL

Hallo,

ich habe in meiner Datenbank zwei Tabellen: Warenkorb und Artikel. Diese haben folgenden Inhalt:

TABLE_WARENKORB
WID Kunde
001 CustomerA
002 CustomerB
003 CustomerC
004 CustomerD
005 CustomerE
006 CustomerF
007 CustomerG
008 CustomerH
009 CustomerI


TABLE_ARTICLE
ID  WID  Article
01  001  Buch
02  001  Föhn
03  001  Bier
04  002  Buch
05  002  Bier
06  003  Buch
07  003  Bier
08  003  Föhn
09  004  Föhn
10  004  Schal
11  005  Föhn
12  005  Buch
13  005  Bier
14  006  Buch
15  006  Bier
16  006  Föhn
17  007  Buch
18  007  Bier
19  008  Schal
20  008  Föhn
21  009  Schal
22  009  Bier
...

Nun möchte ich herausfinden, welche Warenkörbe IDENTISCH sind. Also folgende Ergebnisse:

• Warenkorb 001, 003, 005 und 006 sind identisch.
• Warenkorb 002 und 007 sind identisch
• Warenkorb 004 und 008 sind identisch

Über INTERSECT kann ich ja zwei SELECTS "vergleichen" ... aber damit kann ich ja nur Abfragen ob beispielsweise Warenkorb 001 und 002 identisch sind. Könnte ich zumindest irgendwie abfragen, ob es Warenkörbe gibt, die identisch mit 001 sind?

Danke und Gruß

Hallo be4all,

da hast du dir aber eine interessante Fragestellung ausgedacht 😉
Ich bin mal gespannt, was die User hier für clevere Lösungen posten.

Hier unser Ansatz:

Im mySql gibt es die Funktion group_concat (siehe z.B. hier), mit deren Hilfe du Werte einer Zeile mit einem Separator verknüpfen kannst (analog zu String.Join(string separator, string[] entries) in c#.

Wenn du das jetzt nutzt um die Artikel jedes Warenkorbs aufsteigend sortiert und kommasepariert auszugeben, könntest du mit Hilfe dieses Ergebnisses nach Übereinstimmungen suchen.

Wenn jemand andere Ansätze hat, wäre auch ich daran interessiert.

VG, Florian

Ich habe jetzt mal eine Teillösung für MSSql erarbeitet. Mehr Zeit habe ich gerade leider nicht.
Damit solltest du weiterkommen.

VG, Florian

WITH x (Warenkorb_ID, ArtikelauflistungCsv) AS (
	SELECT DISTINCT(Warenkorb_ID)
		 , (SELECT SUBSTRING(
				 (SELECT ',' + CAST(Artikel_ID AS VARCHAR)
				   FROM dbo.FloWi_Warenkorb_Artikel b 
				  WHERE b.Warenkorb_ID = a.Warenkorb_ID
			   ORDER BY b.Artikel_ID
				  FOR XML PATH('')), 2, 200000)) AS ArtikelauflistungCsv
	  FROM dbo.FloWi_Warenkorb_Artikel a
  ) 
  SELECT * FROM (
	  SELECT x.*
		   , COUNT(*) OVER(PARTITION BY x.ArtikelauflistungCsv) AS AnzahlArtikelImWarenkorb
		 FROM x) AS y
		 WHERE AnzahlArtikelImWarenkorb > 1

Hier das Ergebnis:

Warenkorb_ID ArtikelauflistungCsv     
------------ -------------------------
1            1,2,3                    
3            1,2,3                    
5            1,2,3                    
6            1,2,3                    
2            1,3                      
7            1,3                      
4            2,4                      
8            2,4                      

(8 Zeile(n) betroffen)

Zuvor habe ich diese Tabellen angelegt.

CREATE TABLE [dbo].[FloWi_Warenkorb_Artikel](
	[Warenkorb_ID] [tinyint] NOT NULL,
	[Artikel_ID] [tinyint] NOT NULL,
 CONSTRAINT [PK_FloWi_Warenkorb_Artikel] PRIMARY KEY CLUSTERED 
(
	[Warenkorb_ID] ASC,
	[Artikel_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]


CREATE TABLE [dbo].[FloWi_Artikel](
	[Artikel_ID] [tinyint] NOT NULL,
	[Bezeichnung] [varchar](20) NOT NULL,
 CONSTRAINT [PK_FloWi_Artikel] PRIMARY KEY CLUSTERED 
(
	[Artikel_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

und auch befüllt:

insert into FloWi_Artikel (Artikel_ID, Bezeichnung) values (1, Buch)
insert into FloWi_Artikel (Artikel_ID, Bezeichnung) values (2, Föhn)
insert into FloWi_Artikel (Artikel_ID, Bezeichnung) values (3, Bier)
insert into FloWi_Artikel (Artikel_ID, Bezeichnung) values (4, Schal)

INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (1, 1)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (1, 2)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (1, 3)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (2, 1)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (2, 3)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (3, 1)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (3, 2)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (3, 3)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (4, 2)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (4, 4)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (5, 1)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (5, 2)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (5, 3)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (6, 1)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (6, 2)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (6, 3)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (7, 1)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (7, 3)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (8, 2)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (8, 4)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (9, 3)
INSERT into FloWi_Warenkorb_Artikel (Warenkorb_ID, Artikel_ID) values (9, 4)

Das ist mit einer CTE leicht umzusetzen. Vorrausgesetzt, mySQL kann die Dinger mittlerweile. AFAIK hält sich mySQL noch nicht vollständig an den 99er Standard und kann die deswegen nicht.

Klingt schonmal super und scheint nicht so einfach zu sein.. Habe allerdings einige Fragen:

1. Was ist eine CTE? Google hilft nicht 😦
2. Wozu brauche ich einen Clustered Primary Key?
3. Gibt es eine Datenbank unabhängige Abfrage?

Die Ergebnisse wie von deerhunter sind in Ordnung, eine kommaseparierte Liste wäre auch akzeptabel..

Hallo,

CTE -> Googlesuche nach common table expression

mfG Gü

2. Wozu brauche ich einen Clustered Primary Key?

Hmm, das war die Standard-Einstellung vom Datenbank-Designer, welche ich einfach übernommen habe.

3. Gibt es eine Datenbank unabhängige Abfrage?

Das weiß ich nicht. Wie ich bereits geschrieben habe, bietet mySql bspw. mehr Komfort beim erstellen einer komma-separierten Liste.

VG, Florian

Folgendes hab ich zwar in TSQL fabriziert aber bis auf das Except kommt nicht wirklich was spezifisches vor. Ist wahrscheinlich auch nicht die performateste Variante. 😃

CREATE TABLE Warenkorb
(
	WarenkorbId int,
	ArtikelId int
)

insert Warenkorb values(1, 1)
insert Warenkorb values(1, 2)

insert Warenkorb values(2, 1)

insert Warenkorb values(3, 1)
insert Warenkorb values(3, 2)

insert Warenkorb values(4, 2)


select w1.WarenkorbId, w2.WarenkorbId
from Warenkorb w1
join Warenkorb w2 on w1.WarenkorbId<>w2.WarenkorbId
	AND NOT Exists
	(
		select ArtikelId from Warenkorb tmp1 where tmp1.WarenkorbId=w1.WarenkorbId 
		except 
		select ArtikelId from Warenkorb tmp2 where tmp2.WarenkorbId=w2.WarenkorbId
	)
	AND NOT Exists
	(
		select ArtikelId from Warenkorb tmp1 where tmp1.WarenkorbId=w2.WarenkorbId 
		except 
		select ArtikelId from Warenkorb tmp2 where tmp2.WarenkorbId=w1.WarenkorbId
	)
group by w1.WarenkorbId, w2.WarenkorbId

Lg XXX