MYSQL 双重计数求和

Question

我正在尝试对展示次数表中的 View 进行求和，但有些答案的答案加倍，因为某些广告系列的航类数量超过 1 个。

我正在合并 3 个不同表中的数据。有什么办法可以只返回航类表中的一行吗？

SELECT CAMPAIGN_CPM, CAMPAIGN.CAMPAIGN_ID, CAMPAIGN_NAME, MONTHNAME(IMP_DATE), SUM(VIEWS),  CONCAT(MONTH(IMP_DATE),'-',YEAR(IMP_DATE)) AS MonthYear
FROM IMPRESSION 
INNER JOIN CAMPAIGN
ON IMPRESSION.CAMPAIGN_ID = CAMPAIGN.CAMPAIGN_ID
INNER JOIN FLIGHT
ON FLIGHT.CAMPAIGN_ID = CAMPAIGN.CAMPAIGN_ID
WHERE MONTH(IMP_DATE) = MONTH(now() - interval 1 MONTH) AND CAMPAIGN_CPM > 0 
GROUP BY MONTHNAME(IMP_DATE), IMPRESSION.CAMPAIGN_ID, CAMPAIGN_CPM;

如果需要任何其他信息，请告诉我。

表格

CREATE TABLE `FLIGHT` (
  `FLIGHT_ID` int(11) NOT NULL,
  `FLIGHT_NAME` varchar(255) DEFAULT NULL,
  `FLIGHT_START_DATE` date DEFAULT NULL,
  `FLIGHT_END_DATE` date DEFAULT NULL,
  `CAMPAIGN_FREQUENCY` int(11) DEFAULT NULL,
  `CAMPAIGN_FREQUENCY_PERIOD` varchar(255) DEFAULT NULL,
  `CAMPAIGN_CPM` double DEFAULT NULL,
  `CAMPAIGN_STATUS` varchar(255) DEFAULT NULL,
  `CUSTOM_CAMPAIGN_TYPE` varchar(255) DEFAULT NULL,
  `CAMPAIGN_ID` int(11) NOT NULL,
  PRIMARY KEY (`FLIGHT_ID`),
  KEY `CAMPAIGN` (`CAMPAIGN_ID`),
  CONSTRAINT `CAMPAIGN_FK4` FOREIGN KEY (`CAMPAIGN_ID`) REFERENCES `CAMPAIGN` (`CAMPAIGN_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `IMPRESSION` (
  `IMP_DATE` date NOT NULL,
  `CAMPAIGN_ID` int(11) NOT NULL,
  `BANNER_ID` int(11) DEFAULT NULL,
  `CUSTOMER_ID` int(11) DEFAULT NULL,
  `ADVERTISER_ID` int(11) DEFAULT NULL,
  `PLACEMENT_ID` int(11) DEFAULT NULL,
  `IMPRESSIONS_WITH_DEFAULTS` int(11) DEFAULT NULL,
  `IMPRESSIONS_WITHOUT_DEFAULTS` int(11) DEFAULT NULL,
  `VIEWS` int(11) DEFAULT NULL,
  `DEFAULTS` int(11) DEFAULT NULL,
  `CLICKS` int(11) DEFAULT NULL,
  `IMPRESSION_ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`IMPRESSION_ID`),
  KEY `CAMPAIGN_ID` (`CAMPAIGN_ID`),
  KEY `BANNER_ID` (`BANNER_ID`),
  KEY `CUSTOMER_ID` (`CUSTOMER_ID`),
  KEY `ADVERTISER_ID` (`ADVERTISER_ID`),
  KEY `PLACEMENT_ID` (`PLACEMENT_ID`),
  KEY `CAMPAIGN_ID_2` (`CAMPAIGN_ID`),
  CONSTRAINT `DSF` FOREIGN KEY (`PLACEMENT_ID`) REFERENCES `PLACEMENT` (`PLACEMENT_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `ADVERTISER_FK9` FOREIGN KEY (`ADVERTISER_ID`) REFERENCES `ADVERTISER` (`ADVERTISER_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `BANNER_FK5` FOREIGN KEY (`BANNER_ID`) REFERENCES `BANNER` (`BANNER_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `CAMPAIGN_FK3` FOREIGN KEY (`CAMPAIGN_ID`) REFERENCES `CAMPAIGN` (`CAMPAIGN_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `CUSTOMER_FK5` FOREIGN KEY (`CUSTOMER_ID`) REFERENCES `CUSTOMER` (`CUSTOMER_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=35673 DEFAULT CHARSET=utf8


CREATE TABLE `CAMPAIGN` (
  `CAMPAIGN_ID` int(11) NOT NULL,
  `CAMPAIGN_NAME` varchar(255) DEFAULT NULL,
  `CAMPAIGN_START_DATE` date DEFAULT NULL,
  `CAMPAIGN_END_DATE` date DEFAULT NULL,
  `ADVERTISER_ID` int(11) NOT NULL,
  PRIMARY KEY (`CAMPAIGN_ID`),
  KEY `ADVERTISER` (`ADVERTISER_ID`),
  CONSTRAINT `ADVERTISER_FK2` FOREIGN KEY (`ADVERTISER_ID`) REFERENCES `ADVERTISER` (`ADVERTISER_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

提前致谢。

Answer 1

试试这个:

我添加了 FLIGHT 以显示显示一个航类号，并且我更改了 View 中的位置。

SQL 代码:

SELECT * FROM (
SELECT FLIGHT.FLIGHT_ID, SUM(VIEWS), CAMPAIGN_CPM, CAMPAIGN.CAMPAIGN_ID, CAMPAIGN_NAME, MONTHNAME(IMP_DATE),  CONCAT(MONTH(IMP_DATE),'-',YEAR(IMP_DATE)) AS MonthYear
FROM IMPRESSION 
INNER JOIN CAMPAIGN
ON IMPRESSION.CAMPAIGN_ID = CAMPAIGN.CAMPAIGN_ID
INNER JOIN FLIGHT
ON FLIGHT.CAMPAIGN_ID = CAMPAIGN.CAMPAIGN_ID
WHERE MONTH(IMP_DATE) = MONTH(now() - interval 1 MONTH) AND CAMPAIGN_CPM > 0 
GROUP BY MONTHNAME(IMP_DATE), IMPRESSION.CAMPAIGN_ID, CAMPAIGN_CPM
) a GROUP BY a.CAMPAIGN_ID;

我做了一些小改动，因为我必须用示例数据填充表格。此外，主键和级联也存在一些问题。但是，Every 应该可以在您的版本中工作。

表结构:

CREATE TABLE `FLIGHT` (
  `FLIGHT_ID` int(11) NOT NULL PRIMARY KEY,
  `FLIGHT_NAME` varchar(255) DEFAULT NULL,
  `FLIGHT_START_DATE` date DEFAULT NULL,
  `FLIGHT_END_DATE` date DEFAULT NULL,
  `CAMPAIGN_FREQUENCY` int(11) DEFAULT NULL,
  `CAMPAIGN_FREQUENCY_PERIOD` varchar(255) DEFAULT NULL,
  `CAMPAIGN_CPM` double DEFAULT NULL,
  `CAMPAIGN_STATUS` varchar(255) DEFAULT NULL,
  `CUSTOM_CAMPAIGN_TYPE` varchar(255) DEFAULT NULL,
  `CAMPAIGN_ID` int(11) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `IMPRESSION` (
  `IMP_DATE` date NOT NULL,
  `CAMPAIGN_ID` int(11) NOT NULL,
  `BANNER_ID` int(11) DEFAULT NULL,
  `CUSTOMER_ID` int(11) DEFAULT NULL,
  `ADVERTISER_ID` int(11) DEFAULT NULL,
  `PLACEMENT_ID` int(11) DEFAULT NULL,
  `IMPRESSIONS_WITH_DEFAULTS` int(11) DEFAULT NULL,
  `IMPRESSIONS_WITHOUT_DEFAULTS` int(11) DEFAULT NULL,
  `VIEWS` int(11) DEFAULT NULL,
  `DEFAULTS` int(11) DEFAULT NULL,
  `CLICKS` int(11) DEFAULT NULL,
  `IMPRESSION_ID` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB AUTO_INCREMENT=35673 DEFAULT CHARSET=utf8;

CREATE TABLE `CAMPAIGN` (
  `CAMPAIGN_ID` int(11) NOT NULL PRIMARY KEY,
  `CAMPAIGN_NAME` varchar(255) DEFAULT NULL,
  `CAMPAIGN_START_DATE` date DEFAULT NULL,
  `CAMPAIGN_END_DATE` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

示例数据:

INSERT INTO FLIGHT (FLIGHT_ID, CAMPAIGN_ID,CAMPAIGN_CPM)
VALUES (255060,1,250),
(255080,1,250),
(255070,1,250),
(255020,1,40),
(255025,2,300),
(255040,3,250),
(255045,3,20),
(255046,3,100),
(255071,4,210),
(255055,5,280);

INSERT INTO IMPRESSION (IMP_DATE,CAMPAIGN_ID, VIEWS)
VALUES 
((NOW() - INTERVAL 10 DAY),1,250),
((NOW() - INTERVAL 1 MONTH),2,4050),
((NOW() - INTERVAL 1 MONTH),3,10),
((NOW() - INTERVAL 3 DAY),3,40),
((NOW() - INTERVAL 1 MONTH),3,10),
((NOW() - INTERVAL 3 DAY),4,40),
((NOW() - INTERVAL 1 MONTH),5,1350);

INSERT INTO CAMPAIGN (CAMPAIGN_ID, CAMPAIGN_NAME)
VALUES 
(1,'jkajoiuwejoijdkjjklja'),
(2,'ajiuiouwoihajdhjhhjh'),
(3, 'aauuehxbhchuhnb'),
(4,'aduuiuhzjcnckjnxkj'),
(5,'zzzdfhjfhajkhwkjeajqw');

这是一个SQLFIDDLE演示。试试看。祝您项目顺利。