TOPSIC SQL CONTEST

ソースコード

select 
a.ITEM_CODE as ITEM_A,
b.ITEM_CODE AS ITEM_B,
round(
CAST(count(distinct a.MEMBER_CODE)as real)  * 100 / (select count(distinct MEMBER_CODE) FROM PURCHASE_HISTORY)
, 5) as SUPPORT,
round(
  CAST(count(distinct a.MEMBER_CODE) as real) * 100 /
  (select count(distinct MEMBER_CODE) FROM PURCHASE_HISTORY where ITEM_CODE = a.ITEM_CODE)
, 5) as CONFIDENCE,
round(
  CAST(count(distinct a.MEMBER_CODE) as real) /
  (select count(distinct MEMBER_CODE) FROM PURCHASE_HISTORY where ITEM_CODE = a.ITEM_CODE)
  / (
     CAST((select count(distinct MEMBER_CODE) FROM PURCHASE_HISTORY where ITEM_CODE = b.ITEM_CODE) as real)
    /  (select count(distinct MEMBER_CODE) FROM PURCHASE_HISTORY)
  )
, 5) as LIFT
from  PURCHASE_HISTORY a
join  PURCHASE_HISTORY b
on    a.SESSION_ID = b.SESSION_ID
and   a.ITEM_CODE != b.ITEM_CODE
group by a.ITEM_CODE, b.ITEM_CODE
order by 5 desc,3 desc, 4 desc, 1 desc

提出情報

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

メッセージ

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

3/3

状態

メモリ使用量

データパターン1

83 MB

データパターン2

84 MB

データパターン3

84 MB