TOPSIC SQL CONTEST

ソースコード

SELECT * 
FROM (
SELECT A.ITEM_CODE as 'ITEM_A', B.ITEM_CODE as 'ITEM_B',
round(CAST(COUNT(*) as REAL) /  (SELECT CAST(COUNT(*) as REAL) FROM  (SELECT MEMBER_CODE FROM   PURCHASE_HISTORY GROUP BY  MEMBER_CODE)) * 100,5)  AS 'SUPPORT',
round(CAST(COUNT(*) as REAL) /  (SELECT CAST(COUNT(*) as REAL) FROM  (SELECT MEMBER_CODE FROM   PURCHASE_HISTORY WHERE ITEM_CODE = A.ITEM_CODE GROUP BY  MEMBER_CODE)) * 100,5)  AS 'CONFIDENCE',
round(CAST(COUNT(*) as REAL) /  (SELECT CAST(COUNT(*) as REAL) FROM  (SELECT MEMBER_CODE FROM   PURCHASE_HISTORY WHERE ITEM_CODE = A.ITEM_CODE GROUP BY  MEMBER_CODE)) / ((SELECT CAST(COUNT(*) as REAL) FROM  (SELECT MEMBER_CODE FROM   PURCHASE_HISTORY WHERE ITEM_CODE = B.ITEM_CODE GROUP BY  MEMBER_CODE)) / (SELECT CAST(COUNT(*) as REAL) FROM  (SELECT MEMBER_CODE FROM   PURCHASE_HISTORY GROUP BY  MEMBER_CODE))),5) as 'LIFT'
FROM 
 (select  SESSION_ID, ITEM_CODE , MEMBER_CODE
  FROM    PURCHASE_HISTORY
 ) A
LEFT JOIN
 (select  SESSION_ID, ITEM_CODE , MEMBER_CODE
  FROM    PURCHASE_HISTORY
 ) B
ON A.SESSION_ID = B.SESSION_ID AND A.MEMBER_CODE = B.MEMBER_CODE AND
      A.ITEM_CODE <> B.ITEM_CODE
WHERE B.ITEM_CODE IS NOT NULL
GROUP BY A.ITEM_CODE, B.ITEM_CODE) DataTable
ORDER BY LIFT DESC, SUPPORT DESC, CONFIDENCE DESC, ITEM_A DESC

提出情報

提出日時	2024/05/24 19:04:05
コンテスト	第11回 SQLコンテスト
問題	アソシエーション分析
受験者	katsuno
状態 (詳細)	AC (Accepted: 正答)
メモリ使用量	85 MB

メッセージ

テストケース(通過数／総数)

3/3

状態

メモリ使用量

データパターン1

84 MB

データパターン2

84 MB

データパターン3

85 MB