计算不同ip的相同广告id的印象视图和点击总数。

Question

我有一张这样的桌子：

CREATE TABLE `ad_analytics` (
  `id` int(90) NOT NULL,
  `ad_id` int(90) NOT NULL,
  `advertiser_id` int(90) NOT NULL,
  `publisher_id` int(90) NOT NULL,
  `visitor_ip` varchar(250) NOT NULL,
  `type_ad` varchar(90) NOT NULL,
  `impression` int(90) NOT NULL,
  `view` int(90) NOT NULL,
  `clicks` int(90) NOT NULL,
  `date_event` date NOT NULL,
  `placed_date` date NOT NULL,
  `ending_date` date NOT NULL,
  `cpc` int(60) NOT NULL,
  `cpv` int(60) NOT NULL,
  `cpi` int(60) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

如果同一个IP有四个印象，那么它应该被算作一个特定IP的一个印象。我想要计算不同IP的印象、视图和点击数。另外，如果同一个id有两个不同的ip输入，那么它应该算作2吗？

考虑以下数据：

  INSERT INTO `ad_analytics` (`id`, `ad_id`, `advertiser_id`, `publisher_id`, `visitor_ip`, `type_ad`, `impression`, `view`, `clicks`, `date_event`, `placed_date`, `ending_date`, `cpc`, `cpv`, `cpi`) VALUES
    (1, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (2, 49, 113, 109, '::1', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
    (3, 49, 113, 109, '::1', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (4, 49, 113, 109, '::1', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
    (5, 49, 113, 109, '::2', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (6, 49, 113, 109, '::2', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
    (7, 49, 113, 109, '::2', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (8, 49, 113, 109, '::2', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0),
    (7, 49, 113, 109, '::2', 'Video', 1, 0, 0, '2018-05-01', '2018-05-01', '2018-05-01', 0, 0, 0),
    (8, 49, 113, 109, '::2', 'Video', 0, 0, 1, '2018-05-01', '2018-05-01', '2018-05-01', 3, 0, 0);

这个查询给了我。

SELECT id, ad_id,MAX(impression), MAX(view), MAX(clicks)
FROM ad_analytics
GROUP BY ad_id, visitor_ip;

这个答案是：

(`id`, `ad_id`, `MAX(impression)`, `MAX(view)`, `MAX(clicks)`) VALUES
(1, 49, 1, 0, 1),
(5, 49, 1, 0, 1);

但我期待着这样的回答：

(`id`, `ad_id`, `MAX(impression)`, `MAX(view)`, `MAX(clicks)`) VALUES
(1, 49, 2, 0, 2),

在结果行中，我还需要考虑publisher_id。我怎么能这么做？

Answer 1

我认为可以使用此查询获取所需的预期结果。

select
  ad_id,
  sum(impression) total_impression,
  sum(view) total_views,
  sum(clicks) total_clicks,
from
  (select
     ad_id,
     max(impression)  impression,
     max(view)  view,
     max(clicks)  clicks,
     visitor_ip,
     publisher_id
  from ad_analytics
  where publisher_id = 109
  group by ad_id, visitor_ip) t group by t.ad_id;

更新

从select中删除既不是在group by中也不是聚合的列，并添加了date_event

select
  ad_id,
  sum(impression) total_impression,
  sum(view) total_views,
  sum(clicks) total_clicks,
  date_event
from
  (select
     ad_id,
     max(impression)  impression,
     max(view)  view,
     max(clicks)  clicks,
     visitor_ip,
     date_event
  from ad_analytics
  where publisher_id = 109
  group by ad_id, visitor_ip,date_event) t
group by t.ad_id,t.date_event;