· 8 years ago · Jul 30, 2018, 05:10 PM
1DROP TEMPORARY TABLE IF EXISTS t1;
2CREATE TEMPORARY TABLE t1 AS
3 SELECT
4 attached_site_area_id AS site_area_id,
5 sd24.sa_site_id,
6 sd24.sa_user_id,
7 sd24.dfp_adunit_id
8 FROM attached_site_areas ac
9 JOIN site_area_design_24 sd24 ON sd24.site_area_id = ac.attached_site_area_id
10 WHERE ac.site_area_id IN
11 (588815, 588817, 609925, 591191, 611901, 611916, 610252, 614221, 611803, 616975, 615017, 605660, 600300, 600301, 595364, 595327, 605691, 605689, 605563, 605553, 604109, 612844, 597397, 595489, 595441, 595328, 616862, 619145, 593378, 620204, 593428, 607275, 604110, 603633, 607386, 604114, 604101, 612843, 612538, 606691, 600255, 619524, 587874, 587956, 611516, 610479, 600318, 616587, 604831, 604832, 600385, 598254, 596565, 604026, 615313, 616010, 616030, 616031, 616026, 613215, 613129, 604966, 605072, 610383, 602564, 601671, 601672, 600655, 598253, 615314, 592339, 582980, 598683, 581554, 599730, 620706, 604222, 596704, 596710, 596351, 605631, 599040, 620710, 592786, 617241, 620034, 610657, 620035, 620034, 620035, 617272, 596347, 616830, 596349, 599610, 612233, 599031, 611474, 593846, 590201, 592473, 592831, 615935, 615888, 615880, 618151, 599360, 607807, 619575, 611495, 619838, 620775, 601833, 619020, 602461, 602716, 611959, 611965, 608811, 601169, 616249, 614180, 604594, 605591, 604589, 600201, 611150, 611801, 610201, 619344, 611149, 615030, 615517, 615516, 586552, 587194, 617619, 616434, 593709, 617648, 612734, 619688, 619686, 612559, 605603, 598344, 598345, 598340, 591637, 613957, 613085, 613060, 613062, 613058, 591638, 595573, 613633, 598599, 615239, 613086, 613080, 613084, 598118, 618809, 618815, 589471, 593457)
12 AND sd24.dfp_adunit_id IS NOT NULL;
13
14
15DROP TEMPORARY TABLE IF EXISTS t2;
16CREATE TEMPORARY TABLE t2 AS
17 SELECT
18 cs.site_area_id,
19 sa_site_id,
20 sa_user_id,
21 t1.dfp_adunit_id,
22 sum(count) AS prequests
23 FROM t1
24 JOIN dfp_chain_stat cs ON cs.site_area_id = t1.site_area_id
25 WHERE cs.date BETWEEN "2018-07-27" AND "2018-07-30"
26 GROUP BY cs.site_area_id;
27
28DROP TEMPORARY TABLE IF EXISTS t3;
29CREATE TEMPORARY TABLE t3 AS
30 SELECT
31 t2.site_area_id,
32 sa_site_id,
33 sa_user_id,
34 t2.dfp_adunit_id,
35 prequests,
36 sum(adv_expense) AS adv_expense
37 FROM t2
38 JOIN npm_site_area_stat_cache npm ON t2.site_area_id = npm.site_area_id
39 WHERE npm.date BETWEEN "2018-07-20" AND "2018-07-23"
40 GROUP BY npm.site_area_id;
41
42SELECT
43 t3.site_area_id,
44 sa_site_id,
45 sa_user_id,
46 t3.dfp_adunit_id,
47 prequests,
48 adv_expense,
49 sum(ds.count),
50 adv_expense / sum(ds.count) * 1000 AS pcpm
51FROM t3
52 JOIN dfp_chain_stat ds ON ds.site_area_id = t3.site_area_id
53WHERE ds.date BETWEEN "2018-07-20" AND "2018-07-23"
54GROUP BY ds.site_area_id;