SQL查询 - 从表中查找不同的用户 - 编程

I am trying to solve a problem using SQL query and need some expert's advice.

我试图使用SQL查询解决问题,需要一些专家的建议。

I have below transaction table.

我有下面的交易表。

-- UserID, ProductId, TransactionDate
--   1   ,    2     ,  2014-01-01
--   1   ,    3     ,  2014-01-05
--   2   ,    2     ,  2014-01-02
--   2   ,    3     ,  2014-05-07
           .
           .
           .

What I am trying to achieve is to find all user who purchased more than one product WITHIN 30 DAYS .

我想要实现的是找到所有在30天内购买多个产品的用户。

My query so far is like

到目前为止我的查询是这样的

select UserID, COUNT(distinct ProductID)
from tableA
GROUP BY UserID HAVING COUNT(distinct ProductID) > 1

I am not sure where to apply "WITH IN 30 DAYS" logic in the query .

我不知道在查询中应用“在30天内”逻辑的位置。

The outcome should be :

结果应该是:

1, 2
2, 1

Thanks in advance for your help.

在此先感谢您的帮助。

2 个解决方案

#1

Edit: Within 30 Days

编辑:30天内

SQL Fiddle

SELECT
    a.UserID, 
    COUNT(DISTINCT ProductID)
FROM TableA a
INNER JOIN (
    SELECT UserID, TransactionDate = MAX(TransactionDate)
    FROM TableA
    GROUP BY UserID
) AS t
    ON t.UserID = a.UserID
    AND a.TransactionDate >= DATEADD(DAY, -30, t.TransactionDate)
    AND a.TransactionDate <= t.TransactionDate
GROUP BY a.UserID

You can use GROUP BY YEAR(TransactionDate), MONTH(TransactionDate)

您可以使用GROUP BY YEAR(TransactionDate),MONTH(TransactionDate)

SELECT 
    UserID,
    COUNT(DISTINCT ProductID)
FROM TableA
GROUP BY 
    UserID, YEAR(TransactionDate), MONTH(TransactionDate)
HAVING
    COUNT(DISTINCT ProductID) > 1

SQL查询 - 从表中查找不同的用户

2 个解决方案

#1

更多相关文章

随机推荐