仅计算给定日期的新记录数（如果它们是新的）

发布于 2025-01-06 01:56:42 字数 496 浏览 5 评论 0原文

仅当记录日期是记录所有者的最小（日期）时，我才尝试计算给定日期的新记录数。这是我试图运行的查询：

SELECT COUNT(*) 
  FROM user_total_spends 
 WHERE user_id IN (SELECT user_id 
                     FROM user_total_spends 
                    WHERE MIN(DATE(date_posted)) = '2012-02-07')
   AND merchant_location_id = '4f39b201-4a50-40ff-9cdf-cec51506eaf2' 
   AND date_posted = '2012-02-07';

基本上我想说的是，如果这是第一次遇到该用户/商家，则将其视为该商家的新用户。

当我运行此命令时，我收到 Invalid use of group function 错误。我缺少什么？

原文

I am trying to count the number of new records for a given date only if the date of the record is the min(date) for the record owner. Here is the query I am trying to run:

SELECT COUNT(*) 
  FROM user_total_spends 
 WHERE user_id IN (SELECT user_id 
                     FROM user_total_spends 
                    WHERE MIN(DATE(date_posted)) = '2012-02-07')
   AND merchant_location_id = '4f39b201-4a50-40ff-9cdf-cec51506eaf2' 
   AND date_posted = '2012-02-07';

Basically I am trying to say, if this is the first date this user/merchant is encountered, count it as a new user for this merchant.

When I run this I get a Invalid use of group function error. What am I missing?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

伪装你 2025-01-13 01:56:42

您必须使用 HAVING 来过滤一组记录或聚合函数的条件，因此不要使用 WHERE min(date(date_posted)) = '2012-02-07' 尝试 HAVING min(date(date_posted)) = '2012-02-07'

回复收藏 0 原文

泪之魂 2025-01-13 01:56:42

尝试以下：

 SELECT count(*) FROM user_total_spends 
    WHERE user_id IN (SELECT user_id FROM user_total_spends GROUP BY user_id HAVING min(date(date_posted)) = '2012-02-07')
    AND merchant_location_id = '4f39b201-4a50-40ff-9cdf-cec51506eaf2' AND 
    date_posted = '2012-02-07';

Try below:

 SELECT count(*) FROM user_total_spends 
    WHERE user_id IN (SELECT user_id FROM user_total_spends GROUP BY user_id HAVING min(date(date_posted)) = '2012-02-07')
    AND merchant_location_id = '4f39b201-4a50-40ff-9cdf-cec51506eaf2' AND 
    date_posted = '2012-02-07';

回复收藏 0 原文

~没有更多了~