2 sütun ustida so'rovlarni qanday qilib so'rash mumkin

Ikkala jamoa bir-biriga qanchalik tez-tez o'ynaganligini ko'rsatadigan SQL so'rovini yozishga harakat qilaman.

Id | Team1 | Team2 | Date 
1  |   A   |   B   | 25/5/11
2  |   B   |   A   | 26/5/11
3  |   A   |   C   | 27/5/11
4  |   C   |   B   | 28/5/11
5  |   A   |   B   | 28/5/11

natija quyidagicha bo'lishi kerak:

A vs B => 3
A vs C => 1
C vs B => 1 

A-B va B-A-ni har xil deb hisoblash oson so'rovdir. Lekin ularni bir-biriga sanashga qodir emasman.

Har qanday taklif bormi?

6
Qanday SQL versiyasidan foydalanmoqdasiz?
qo'shib qo'ydi muallif leoinfo, manba
Jamoa nomlari har qanday narsa bo'lishi mumkin. Men uni tezroq yozish uchun A va B dan foydalandim.
qo'shib qo'ydi muallif Sorskoot, manba
Bu holatda mening shaxsiy ma'lumotlar bazam uchun MySQL-dan foydalanmoqdaman ...
qo'shib qo'ydi muallif Sorskoot, manba
har bir jamoani ifodalovchi raqam bormi? ya'ni jamoalar jadvalidanmi?
qo'shib qo'ydi muallif galchen, manba

4 javoblar

Guruhlarni guruhlashdan oldin guruhlarni tartibga solish uchun pastki so'rovlardan foydalanganman.

SELECT first_team, second_team, count(*)
FROM (
    SELECT 
        CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END AS first_team,
        CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END AS second_team
    FROM table
) a
GROUP BY first_team, second_team;
6
qo'shib qo'ydi
Bu ishlaydi, lekin u indekslarni umuman ishlatmaydi, shuning uchun sizda ko'plab jamoalar mening javobimdan foydalanadi.
qo'shib qo'ydi muallif Ariel, manba
SELECT Team1, Team2, SUM(num) FROM (
   SELECT Team1, Team2, COUNT(*) num
   FROM table_name
   GROUP BY Team1, Team2
  UNION ALL
   SELECT Team2, Team1, COUNT(*) num
   FROM table_name
   GROUP BY Team2, Team1
) combined
WHERE Team1 < Team2
GROUP BY Team1, Team2

Tartibga solish: Zarur bo'lganda guruhlarni qayta tiklash.

Eslatma: Ushbu boshqa versiyalarda berilgan CASE yordamida versiyalarga nisbatan tezroq ishlaydi, chunki u indekslardan to'liq foydalanishni ta'minlaydi.

Edit2: indekslar bilan qaerda bo'lishni tezroq ko'chirdi.

5
qo'shib qo'ydi
@Ariel - Men serverga keraksiz yukni qo'ygan deb o'ylayman. Mening postimga e'tibor bering va "Style 4" yorlig'ini tekshiring. Ijro rejasiga asosan, bu taxminan 60% ga tezroq bo'lishi kerak.
qo'shib qo'ydi muallif leoinfo, manba
@Shark Qani, buni o'tkazib yubordim. Muammo yo'q, javobim yangilandi.
qo'shib qo'ydi muallif Ariel, manba
@Sorskoot Ikkita indeksni qo'ying: Team1, Team2 va Team2, Team1 bo'yicha ikkinchi ko'rsatkich
qo'shib qo'ydi muallif Ariel, manba
Men indekslar bilan ham tezroq tahrir qildim. qaerda aslida ixtiyoriy - agar siz uni tashlab qo'ysangiz, har bir guruhni ikki marta olasiz - bu sizga yordam berishi mumkin, chunki faqatgina Team1 bu guruh uchun ikkala ustunni tekshirish o'rniga maxsus jamoa.
qo'shib qo'ydi muallif Ariel, manba
@leoinfo bu juda tez, chunki sizda juda oz ma'lumotlar mavjud. Siz ma'lumotlar bazasini aslida tartiblashtirish, guruhlash va har bir qatorni hisoblash uchun majbur qilyapsiz. Menga faqatgina indeksga o'tish kerak va indeksdagi bir xil barglarning sonini to'g'ridan-to'g'ri olish kerak, ularni aslida sanash kerak emas, ularni ham tartiblashtirish yoki guruhlash kerak emas - indeks.
qo'shib qo'ydi muallif Ariel, manba
Bu mening fikrimcha :)
qo'shib qo'ydi muallif Sorskoot, manba
Jadval juda katta o'sishi mumkin ... Shunday qilib indekslarni ishlatish ehtimoldan yiroq ... Rahmat ..
qo'shib qo'ydi muallif Sorskoot, manba
Bu to'g'ri natija bermaydi. Bu so'rovda A -> B va B -> A o'rtasidagi farq mavjud. O. B va boshqalar A bilan birlashtirilgan A va B ni qidiradi.
qo'shib qo'ydi muallif user596075, manba
@Ariel yaxshi ishlaydi! +1
qo'shib qo'ydi muallif user596075, manba

Maqsadingizga erishishning bir necha yo'li mavjud:

SELECT Teams, Games = COUNT(*) FROM 
(
  SELECT 
    Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' +
            CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
  FROM MY_TABLE 
) AS T
GROUP BY Teams

Yoki SQL 2005/2008 dan foydalansangiz

;WITH T AS (
  SELECT 
    Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' +
            CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
  FROM MY_TABLE 
)
SELECT Teams, Games = COUNT(*) FROM T GROUP BY Teams

Yuqoridagi ikkala narsa sizga bir xil natija beradi

/*
Teams     Games
-------|------
A vs B  3
A vs C  1
B vs C  1
*/

Siz bilan o'ynashingiz mumkin bo'lgan skript quyidagicha:

/* TEST DATA */
DECLARE @t AS TABLE ( ID INT, Team1 CHAR(1), Team2 CHAR(1), playdate [DATETIME] )
INSERT INTO @t (Team1 , Team2 , playdate)
          SELECT 'A' , 'B', '20110525'
UNION ALL SELECT 'B' , 'A', '20110526'    
UNION ALL SELECT 'A' , 'C', '20110527'    
UNION ALL SELECT 'C' , 'B', '20110528'    
UNION ALL SELECT 'A' , 'B', '20110528'    


/* STYLE 1 */    
;WITH T AS (
  SELECT 
    Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' +
            CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
  FROM @t 
)
SELECT Teams, Games = COUNT(*) FROM T GROUP BY Teams

/* STYLE 2 */
SELECT Teams, Games = COUNT(*) FROM 
(
  SELECT 
    Teams = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END + ' vs ' +
            CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
  FROM @t 
) AS T
GROUP BY Teams

/ * YoKI, bu faqat ustunlarni * /

/* STYLE 3 */    
;WITH T AS (
  SELECT 
      Team1 = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END 
    , Team2 = CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
  FROM @t 
)
SELECT Team1 , Team2, Games = COUNT(*) FROM T GROUP BY Team1 , Team2

/* STYLE 4 */
SELECT Team1 , Team2, Games = COUNT(*) FROM 
(
  SELECT 
      Team1 = CASE WHEN Team1 < Team2 THEN Team1 ELSE Team2 END 
    , Team2 = CASE WHEN Team1 < Team2 THEN Team2 ELSE Team1 END
  FROM @t 
) AS T
GROUP BY Team1 , Team2
1
qo'shib qo'ydi
SELECT 
  (CASE WHEN Team1Team2 THEN Team1 ELSE Team2) Team2,
  COUNT(*)  cnt
FROM <table> 
GROUP BY 
  (CASE WHEN Team1Team2 THEN Team1 ELSE Team2) Team2
1
qo'shib qo'ydi