मेरे पास एकाधिक सबक्वायरी के साथ एक प्रश्न है कि यह गिनने के लिए कि एक निश्चित लेनदेन में शामिल कितने उपयोगकर्ता स्थिति पर निर्भर करते हैं।

SELECT * FROM (
    SELECT
    DATE(changes.created_at) AS `date`,
    SUM(IF(changes.status = 15, 1, 0)) AS installed_daily,
    SUM(IF(changes.status = 3, 1, 0)) AS port_reserved,
    (
      SELECT COUNT(DISTINCT created_by) FROM applicant_state_changes
      WHERE DATE(created_at) = DATE(changes.created_at) AND status = 3
    ) AS user_port_reserved,
    SUM(IF(changes.status = 5, 1, 0)) AS document_validated,
    (
      SELECT COUNT(DISTINCT created_by) FROM applicant_state_changes
      WHERE DATE(created_at) = DATE(changes.created_at) AND status = 5
    ) AS user_document_validated,
    SUM(IF(changes.status = 7, 1, 0)) AS account_created,
    (
      SELECT COUNT(DISTINCT created_by) FROM applicant_state_changes
      WHERE DATE(created_at) = DATE(changes.created_at) AND status = 7
    ) AS user_account_created,
    SUM(IF(changes.status = 11, 1, 0)) AS jo_created,
    (
      SELECT COUNT(DISTINCT created_by) FROM applicant_state_changes
      WHERE DATE(created_at) = DATE(changes.created_at) AND status = 11
    ) AS user_jo_created
    FROM applicant_state_changes AS changes
    GROUP BY DATE(changes.created_at)
    LIMIT 100 OFFSET 0
) a
ORDER BY date ASC;

इसमें करीब 130 सेकेंड का समय लगता है। उपश्रेणियों के बिना, मेरी क्वेरी में केवल 0.5 सेकंड तक का समय लगता है।

2
See More 21 मई 2019, 07:58

1 उत्तर

सबसे बढ़िया उत्तर

जब आप अभिव्यक्ति करते हैं तो आप केस का उपयोग करके सशर्त एकत्रीकरण का उपयोग कर सकते हैं

 SELECT
    DATE(changes.created_at) AS `date`,
    SUM(IF(changes.status = 15, 1, 0)) AS installed_daily,
    SUM(IF(changes.status = 3, 1, 0)) AS port_reserved,
    count(distinct case when status = 3 then created_by end) as user_port_reserved
    ,
    SUM(IF(changes.status = 5, 1, 0)) AS document_validated,
    count(distinct case when status = 5 then created_by  end) AS user_document_validated,
    SUM(IF(changes.status = 7, 1, 0)) AS account_created,
    count(distinct case when status = 7 then 1 end) AS user_account_created,
    SUM(IF(changes.status = 11, 1, 0)) AS jo_created,
    count(distinct case when status = 11 then created_by  end) AS user_jo_created
    FROM applicant_state_changes AS changes where 
    GROUP BY DATE(changes.created_at)
    LIMIT 100 OFFSET 0
4
Fahmi 21 मई 2019, 05:06