Addate model plan "out-there" click spend
On January 1, 2020 X2 plan A 1 1 1
On January 1, 2020 MR plan B 1 1 1
On January 2, 2020 MR plan B 1 1 1
On January 2, 2020 X2 plan A 1 1 1
On January 3, 2020 X2 plan A 1 1 1
On January 3, 2020 MR plan B 1 1 1
On January 3, 2020 MR plan C 1 1 1
On January 1, 2020 X2 plan B 1 1 1
On January 2, 2020 X2 plan B 1 1 1
On January 3, 2020 X2 plan B 1 1 1
Table_02
Brdate link model view unit_ordered sales
On January 1, 2020 links 1 X2 1 1 1
On January 1, 2020 links 3 MR 1 1 1
On January 2, 2020 links 3 MR 1 1 1
On January 2, 2020 links 1 X2 1 1 1
On January 3, 2020 links 1 X2 1 1 1
On January 3, 2020 links 3 MR 1 1 1
On January 1, 2020 links 2 X2 1 1 1
On January 2, 2020 links 2 X2 1 1 1
On January 3, 2020 links 2 X2 1 1 1
Here is hope to get the data table
Brdate model sum items: the view sum items: unit_ordered summation item: sales
2020/1/1 MR 1 1 1
2020/1/1 2 2 2 X2
2020/1/2 MR 1 1 1
2020/1/2 2 2 2 X2
2020/1/3 MR 1 1 1
2020/1/3 2 2 2 X2
This process using mysql how to implement, thanks
CodePudding user response:
The select addate adbrdate, model, the sum (impress) sum1, sum (click) sum2, sum (spend) sum3The from table_01
Group by addate, model
Union all
The select brdate adbrdate, model, the sum (view) sum1, sum (unit_ordered) sum2, sum (sales) sum3
The from table_02
Group by brdate model;
CodePudding user response:
Thank you for your reply, you understand the wrong I mean, after the merger of the field, I need the date, model, impress, click, spend, view, unit_ordered, sales,"Out-there" and the view, click and unit_ordered, spend and sales doesn't add
I wrote a logical, help to have a look at why not open the results
Logic: a table grouping are summed up first group by date, model, so the date and the model can be only value as the primary key of a new table
To group b table are summed up to group by date, model, so the date and the model can become a new form the unique value of b as the primary key
So there were only two new table a primary key value, can the two new tables for inner join, but the result is always an error to try
Help to have a look at what the reason, the insert expression has been posted, thank you
The CREATE TABLE ` a ` (
` addate ` date NULL DEFAULT NULL,
` model ` varchar (255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
` plan ` varchar (255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
` impress ` int (11) NULL DEFAULT NULL,
` click ` int (11) NULL DEFAULT NULL,
` spend ` decimal (10, 0) NULL DEFAULT NULL
)
INSERT INTO ` a ` VALUES (' 2020-01-01 ', 'X2', 'plan a', 1, 1, 1);
INSERT INTO ` a ` VALUES (' 2020-01-01 ', 'MR35', 'plan B', 1, 1, 1);
INSERT INTO ` a ` VALUES (' 2020-01-02 ', 'MR35', 'plan B', 1, 1, 1);
INSERT INTO ` a ` VALUES (' 2020-01-02 ', 'X2', 'plan a', 1, 1, 1);
INSERT INTO ` a ` VALUES (' 2020-01-03 ', 'X2', 'plan a', 1, 1, 1);
INSERT INTO ` a ` VALUES (' 2020-01-03 ', 'MR35', 'plan B', 1, 1, 1);
INSERT INTO ` a ` VALUES (' 2020-01-03 ', 'MR35', 'C', 1, 1, 1);
INSERT INTO ` a ` VALUES (' 2020-01-01 ', 'X2', 'plan B', 1, 1, 1);
INSERT INTO ` a ` VALUES (' 2020-01-02 ', 'X2', 'plan B', 1, 1, 1);
INSERT INTO ` a ` VALUES (' 2020-01-03 ', 'X2', 'plan B', 1, 1, 1);
The CREATE TABLE ` ` b (
` brdate ` date NULL DEFAULT NULL,
` link ` varchar (255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
` model ` varchar (255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
` view ` int (11) NULL DEFAULT NULL,
` unit_ordered ` int (11) NULL DEFAULT NULL,
` sales ` decimal (10, 0) NULL DEFAULT NULL
)
INSERT INTO ` ` b VALUES (' 2020-01-01 ', '1' link, 'X2, 1, 1, 1);
INSERT INTO ` ` b VALUES (' 2020-01-01 ', '3' link, 'MR35', 1, 1, 1);
INSERT INTO ` ` b VALUES (' 2020-01-02 ', '3' link, 'MR35', 1, 1, 1);
INSERT INTO ` ` b VALUES (' 2020-01-02 ', '1' link, 'X2, 1, 1, 1);
INSERT INTO ` ` b VALUES (' 2020-01-03 ', '1' link, 'X2, 1, 1, 1);
INSERT INTO ` ` b VALUES (' 2020-01-03 ', '3' link, 'MR35', 1, 1, 1);
INSERT INTO ` ` b VALUES (' 2020-01-01 ', '2' link, 'X2, 1, 1, 1);
INSERT INTO ` ` b VALUES (' 2020-01-02 ', '2' link, 'X2, 1, 1, 1);
INSERT INTO ` ` b VALUES (' 2020-01-03 ', '2' link, 'X2, 1, 1, 1);
The select m. ddate, m.m odel, the sum (m.i m), sum (n.s a)
The from
(select a.a ddate, arjun odel, the sum (Anderson mpress) as im
From a
Group by a.a ddate, arjun m odel) - date and after the grouping a summation model can be assembled into unique value
Inner JOIN
(select b.b rdate, b.m odel, the sum (b.s ales) as sa
The from b
Group by b.b rdate, b.m odel) n - for group b after sum model and date can be combined into a unique value
On m. ddate=n.b rdate and m.m odel=n.m odel, the last inner merge join