查找本月未出现的行的SQL查询

Question

我希望找到上个月有销售但本月没有销售的卖家数量。

我有一个可行的查询，但我认为它不是很高效，而且我还没有想出如何处理所有月份的情况。

SELECT count(distinct user_id) as users
FROM transactions
WHERE MONTH(date) = 12
AND YEAR(date) = 2015
AND transactions.status = 'COMPLETED'
AND transactions.amount > 0
AND transactions.user_id NOT IN 
(
    SELECT distinct user_id
    FROM transactions
    WHERE MONTH(date) = 1
    AND YEAR(date) = 2016
    AND transactions.status = 'COMPLETED'
    AND transactions.amount > 0
)

表格的结构如下：

+---------+------------+-------------+--------+
| user_id |    date    |   status    | amount |
+---------+------------+-------------+--------+
|       1 | 2016-01-01 | 'COMPLETED' | 1.00   |
|       2 | 2015-12-01 | 'COMPLETED' | 1.00   |
|       3 | 2015-12-01 | 'COMPLETED' | 2.00   |
|       1 | 2015-12-01 | 'COMPLETED' | 3.00   |
+---------+------------+-------------+--------+

因此，此情况下，ID为2和3的用户本月没有进行销售。

- Abs

---

你为什么认为它效率低？它运行缓慢吗？ - AdamMc331

就当前日期和月份而言，您可以使用CURDATE()函数并根据需要操作该值，或者使用变量输入一个日期，并让查询计算上个月。 - AdamMc331

@McAdam331 这不是很有效率，因为函数无法使用索引。 - Strawberry

Answer 1

使用条件聚合：

SELECT count(*) as users
FROM
 (
    SELECT user_id
    FROM transactions
                       -- 1st of previous month
    WHERE date BETWEEN SUBDATE(SUBDATE(CURRENT_DATE, DAYOFMONTH(CURRENT_DATE)-1), interval 1 month) 
                       -- end of current month
                   AND LAST_DAY(CURRENT_DATE)
    AND transactions.status = 'COMPLETED'
    AND transactions.amount > 0
    GROUP BY user_id
           -- any row from previous month
    HAVING MAX(CASE WHEN date < SUBDATE(CURRENT_DATE, DAYOFMONTH(CURRENT_DATE)-1)
                    THEN date 
               END) IS NOT NULL
           -- no row in current month
       AND MAX(CASE WHEN date >= SUBDATE(CURRENT_DATE, DAYOFMONTH(CURRENT_DATE)-1)
                    THEN date 
               END) IS NULL           
 ) AS dt

SUBDATE(CURRENT_DATE, DAYOFMONTH(CURRENT_DATE)-1) = 当月第一天

SUBDATE(当月第一天, interval 1 month) = 上月第一天

LAST_DAY(CURRENT_DATE) = 当月最后一天

- dnoeth

---

太棒了，这比我的查询快多了，谢谢！现在我只需使用我的应用程序代码循环遍历它，并在每个循环中简单地更改当前日期。 - Abs

Answer 2

如果您想泛化它，可以使用curdate()获取当前月份，以及DATE_SUB(curdate(), INTERVAL 1 MONTH)获取上个月份（不过对于一月/十二月需要进行一些if子句处理）：

SELECT count(distinct user_id) as users
FROM transactions
WHERE MONTH(date) = MONTH(DATE_SUB(curdate(), INTERVAL 1 MONTH))
AND transactions.status = 'COMPLETED'
AND transactions.amount > 0
AND transactions.user_id NOT IN 
(
    SELECT distinct user_id
    FROM transactions
    WHERE MONTH(date) = MONTH(curdate())
    AND transactions.status = 'COMPLETED'
    AND transactions.amount > 0
)

就效率而言，我认为这个没有问题。

- Nir Levy

---

它与原始版本存在相同的缺陷，因为函数无法使用索引。如果您可以将其重写为范围查询，则可以使用索引并且速度会更快。 - Strawberry

Answer 3

以下内容应该相当高效。为了使其更加高效，您需要提供表定义和EXPLAIN。

SELECT COUNT(DISTINCT user_id) users
  FROM transactions t
  LEFT 
  JOIN transactions x
    ON x.user_id = t.user_id
   AND x.date BETWEEN '2016-01-01' AND '2016-01-31'
   AND x.status = 'COMPLETED'
   AND x.amount > 0
 WHERE t.date BETWEEN '2015-12-01' AND '2015-12-31'
   AND t.status = 'COMPLETED'
   AND t.amount > 0
   AND x.user_id IS NULL;

- Strawberry

Answer 4

思考一下：
您可以创建每月用户ID的聚合列表，表示该月所有唯一的买家。在您的应用程序中，您只需要减去两个相关月份，就可以得到仅在其中一个月份进行了销售的所有用户ID。
请参见以下查询和后处理示例。

为了使您的查询高效，我建议在表格交易上至少使用2列索引[状态，金额]。但是，为了防止查询必须在实际表格中查找数据，您甚至可以创建一个4列索引[状态，金额，日期，用户ID]，这应该进一步提高查询性能。

Postgres（v9.0+，已测试）

SELECT   (DATE_PART('year', t.date) || '-' || DATE_PART('month', t.date)) AS d,
         STRING_AGG( DISTINCT t.user_id::TEXT, ',' ) AS buyers
FROM     transactions t
WHERE    t.status = 'COMPLETED'
AND      t.amount > 0
GROUP BY DATE_PART('year', t.date),
         DATE_PART('month', t.date)
ORDER BY DATE_PART('year', t.date),
         DATE_PART('month', t.date)
;

MySQL（未经测试）

SELECT   (YEAR(t.date) || '-' || MONTH(t.date)) AS d,
         GROUP_CONCAT( DISTINCT t.user_id ) AS buyers
FROM     transactions t
WHERE    t.status = 'COMPLETED'
AND      t.amount > 0
GROUP BY YEAR(t.date), MONTH(t.date)
ORDER BY YEAR(t.date), MONTH(t.date)
;

Ruby（后处理示例）

db_result                   = ActiveRecord::Base.connection_pool.with_connection { |con| con.execute( db_query ) }
unique_buyers               = db_result.map{|e|[e['d'],e['buyers'].split(',')]}.to_h

buyers_dec15_but_not_jan16  = unique_buyers['2015-12'] - unique_buyers['2016-1']
buyers_nov15_but_not_dec16  = unique_buyers['2015-11']||[] - unique_buyers['2015-12']
...(and so on)...

- Andreas Rayo Kniep