计数所有唯一记录 - Count all unique records

Count all unique records

本文关键字：唯一记录 | 更新日期: 2023-09-27

我有一个数据库，格式如下:

myTable
productgroupID | productID | views | date
1              | 10        | 25    | 2013-05-23 
4              | 105       | 15    | 2013-05-23 
7              | 60        | 65    | 2013-05-23
7              | 60        | 55    | 2013-05-22
7              | 60        | 45    | 2013-05-21

现在我要对五月份某产品的所有视图求和。结果应为:

productgroupID | productID | viewed | month
7              | 60        | 165    | 2013-05-01
1              | 10        | 25     | 2013-05-01 
4              | 105       | 15     | 2013-05-01

我尝试了下面的查询，但是这给了我一个特定productgroupID的所有视图。但我需要唯一productgroupID & &;productID .

SELECT COUNT( views ) AS viewed, productgroupID FROM product_stats_daily GROUP BY productgroupID

如果您想要视图总计，那么您可以使用sum()聚合函数，然后您可以按month和year对日期进行分组:

select productGroupId,
  productId,
  sum(views) viewed,
  month(date) Month,
  year(date) Year
from myTable
group by productGroupId, productId, month(date), year(date);

参见SQL Fiddle with Demo

您也可以使用Date_Format以您想要的格式获取日期:

select productGroupId,
  productId,
  sum(views) viewed,
  DATE_FORMAT(date, '%Y-%m-01') date
from myTable
group by productGroupId, productId, DATE_FORMAT(date, '%Y-%m-01')

参见SQL Fiddle with Demo

COUNT将计算行数，而SUM将计算检索到的行值。

所以你的查询变成:

SELECT SUM( views ) AS viewed, productgroupID
FROM product_stats_daily
GROUP BY productgroupID

可能有一种更优雅的方法来强制日期到月初，但这应该可以工作:

SELECT
  ProductGroupID,
  ProductID,
  SUM(views) AS viewed,
  DATE_FORMAT(date, '%Y-%m-01') AS Month
FROM myTable
GROUP BY
  ProductGroupID,
  ProductID,
  DATE_FORMAT(date, '%Y-%m-01')