gpt4 book ai didi

MYSQL 双重计数求和

转载 作者:行者123 更新时间:2023-11-30 00:45:36 25 4
gpt4 key购买 nike

我正在尝试对展示次数表中的 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

提前致谢。

最佳答案

试试这个:

我添加了 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演示。试试看。祝您项目顺利。

关于MYSQL 双重计数求和,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/21387115/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com