获取两个不同日期列的计数并按日期分组 [英] Get count on two different date columns and group by date

问题描述

我有一个包含两个DATE列的表. TS_customer和TS_verified

我正在寻找一种获取结果的方法，其中在第一列中有一些日期，有人创建用户(TS_customer)或有人通过验证(TS_verified).

在第二列中，我想按第一列对count(TS_customer)进行分组. 我希望第三列按第一列进行计数(TS_verified).

在注册日期可能要验证0位客户，在另一种情况下，在某人通过验证的日期要验证0位客户.

我想这应该很容易，但是我现在已经花了很多时间了.非常感谢您的帮助.我需要在excel中使用它，因此我基本上希望有一天注册了多少客户，并且有多少人经过验证，而不必麻烦进行两个选择并手动组合它们.

链接到SQLfiddle http://sqlfiddle.com/#!2/b14fc /1/0

谢谢

解决方案

首先，我们需要列出日期.

看起来像这样 http://sqlfiddle.com/#!2/b14fc/14/0 :

   SELECT DISTINCT days
     FROM (
       SELECT DISTINCT DATE(TS_customer) days
         FROM customer
        UNION 
       SELECT DISTINCT DATE(TS_verified) days
         FROM customer

     ) AS alldays
 WHERE days IS NOT NULL
 ORDER BY days

接下来，我们需要按天汇总客户数量. http://sqlfiddle.com/#!2/b14fc/16/0 :

SELECT DATE(TS_customer) days, COUNT(TS_customer)
  FROM customer
 GROUP BY days

每天进行验证的摘要也很容易.

接下来，我们需要将这三个子查询结合在一起 http://sqlfiddle.com/# ！2/b14fc/29/0 .

SELECT alldays.days, custcount, verifycount
  FROM (
           SELECT DISTINCT DATE(TS_customer) days
             FROM customer
            UNION 
           SELECT DISTINCT DATE(TS_verified) days
             FROM customer
       ) AS alldays
   LEFT JOIN (
      SELECT DATE(TS_customer) days, COUNT(TS_customer) custcount
        FROM customer
       GROUP BY days
      ) AS cust ON alldays.days = cust.days
   LEFT JOIN (
      SELECT DATE(TS_verified) days, COUNT(TS_verified) verifycount
        FROM customer
       GROUP BY days
      ) AS verif ON alldays.days = verif.days
  WHERE alldays.days IS NOT NULL
  ORDER BY alldays.days

最后，如果要在没有任何客户和/或验证的情况下显示0而不是显示(null)，请将SELECT行更改为此http://sqlfiddle.com/#!2/b14fc/1/0

Thanks

解决方案

First, we need the list of days.

That looks like this http://sqlfiddle.com/#!2/b14fc/14/0:

   SELECT DISTINCT days
     FROM (
       SELECT DISTINCT DATE(TS_customer) days
         FROM customer
        UNION 
       SELECT DISTINCT DATE(TS_verified) days
         FROM customer

     ) AS alldays
 WHERE days IS NOT NULL
 ORDER BY days

Next we need a summary of customer counts by day. That's pretty easy http://sqlfiddle.com/#!2/b14fc/16/0:

SELECT DATE(TS_customer) days, COUNT(TS_customer)
  FROM customer
 GROUP BY days

The summary of verifications by day is similarly easy.

Next we need to join these three subqueries together http://sqlfiddle.com/#!2/b14fc/29/0.

SELECT alldays.days, custcount, verifycount
  FROM (
           SELECT DISTINCT DATE(TS_customer) days
             FROM customer
            UNION 
           SELECT DISTINCT DATE(TS_verified) days
             FROM customer
       ) AS alldays
   LEFT JOIN (
      SELECT DATE(TS_customer) days, COUNT(TS_customer) custcount
        FROM customer
       GROUP BY days
      ) AS cust ON alldays.days = cust.days
   LEFT JOIN (
      SELECT DATE(TS_verified) days, COUNT(TS_verified) verifycount
        FROM customer
       GROUP BY days
      ) AS verif ON alldays.days = verif.days
  WHERE alldays.days IS NOT NULL
  ORDER BY alldays.days

Finally, if you want 0 displayed rather than (null) for days when there weren't any customers and/or verifications, change the SELECT line to this http://sqlfiddle.com/#!2/b14fc/30/0.

SELECT alldays.days, 
       IFNULL(custcount,0) AS custcount, 
       IFNULL(verifycount,0) AS verifycount

See how that goes? We build up your result set step by step.

这篇关于获取两个不同日期列的计数并按日期分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！