一条SQL语句的剖析

查询发了5个以上优质产品的会员数量

SQL> SELECT/*+ parallel(a 4)*/  DISTINCT
  2   count(a.Admin_Member_Id) over() AS admin_member_cnt
  3    FROM ENDSS.EN_WTO_PROD_WIDE_FATDT0 A
  4   WHERE A.IS_RIGHT_PROD = 'Y'
  5   GROUP BY a.admin_member_id HAVING COUNT(a.product_id) > 5;

 

ADMIN_MEMBER_CNT
----------------
            4902

 

剖析这条SQL语句 

第一步

SQL> SELECT/*+ parallel(a 4)*/
  2   a.Admin_Member_Id  AS admin_member_cnt
  3    FROM ENDSS.EN_WTO_PROD_WIDE_FATDT0 A
  4   WHERE A.IS_RIGHT_PROD = 'Y'
  5   GROUP BY a.admin_member_id HAVING COUNT(a.product_id) > 5;--按admin_member_id进行分组，查询出产品数>5的所有会员id

 

ADMIN_MEMBER_CNT
--------------------------------
cnkding
worldfone
hkthu
ecmonster
cnsunprince
cnwisecorp
vstarchina

 

 

第二步

SQL> SELECT/*+ parallel(a 4)*/
  2   count(a.Admin_Member_Id) over()  AS admin_member_cnt
  3    FROM ENDSS.EN_WTO_PROD_WIDE_FATDT0 A
  4   WHERE A.IS_RIGHT_PROD = 'Y'
  5   GROUP BY a.admin_member_id HAVING COUNT(a.product_id) > 5;--count() over()分析函数统计admin_member_id的数量。分析函数不会改变数据量，且分析函数是最后执行的。

 

ADMIN_MEMBER_CNT
----------------
            4902
            4902
            4902
            4902
            4902
            4902
            4902

 

第三步

SQL> SELECT/*+ parallel(a 4)*/  DISTINCT
  2   count(a.Admin_Member_Id) over() AS admin_member_cnt
  3    FROM ENDSS.EN_WTO_PROD_WIDE_FATDT0 A
  4   WHERE A.IS_RIGHT_PROD = 'Y'
  5   GROUP BY a.admin_member_id HAVING COUNT(a.product_id) > 5;--然后再去重

 

ADMIN_MEMBER_CNT
----------------
            4902