· 8 years ago · Apr 13, 2018, 01:36 AM
1CREATE TABLE IF NOT EXISTS "data" (
2 "id" SERIAL,
3 "hash" CHARACTER VARYING(255) NOT NULL,
4 "source" CHARACTER VARYING(255) NOT NULL,
5 "isFiltered" BOOLEAN NOT NULL,
6 "campaignId" INTEGER NOT NULL,
7 "data" JSON NOT NULL,
8 "meta" JSON NOT NULL,
9 "modifiedReason" CHARACTER VARYING(255) NULL DEFAULT NULL,
10 "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL,
11 "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL,
12 PRIMARY KEY ("id")
13);
14
15CREATE TABLE IF NOT EXISTS "campaign" (
16 "id" SERIAL,
17 "userId" INTEGER NOT NULL,
18 "userCompanyId" INTEGER NOT NULL,
19 "type" CHARACTER VARYING(255) NULL DEFAULT NULL,
20 "title" CHARACTER VARYING(255) NULL DEFAULT NULL,
21 "description" CHARACTER VARYING(255) NULL DEFAULT NULL,
22 "sources" CHARACTER VARYING(255)[] NULL DEFAULT NULL,
23 "configuration" JSON NOT NULL,
24 "active" BOOLEAN NULL DEFAULT true,
25 "excludedUserNames" CHARACTER VARYING(255)[] NULL DEFAULT NULL,
26 "limit" INTEGER NULL DEFAULT 0,
27 "startAt" TIMESTAMP WITH TIME ZONE NOT NULL,
28 "endAt" TIMESTAMP WITH TIME ZONE NOT NULL,
29 "removedAt" TIMESTAMP WITH TIME ZONE NULL DEFAULT NULL,
30 "createdAt" TIMESTAMP WITH TIME ZONE NOT NULL,
31 "updatedAt" TIMESTAMP WITH TIME ZONE NOT NULL,
32 PRIMARY KEY ("id")
33);
34
35INSERT INTO "campaign" ("id", "userId", "userCompanyId", "type", "title", "description", "sources", "configuration", "active", "excludedUserNames", "limit", "startAt", "endAt", "removedAt", "createdAt", "updatedAt") VALUES
36 (1, 1, 1, E'test', E'Test', E'Test', E'{a}', E'{"query":{"accounts":[],"hashtags":["GavinTest1234","XYZ"]}}', E'true', NULL, 0, E'2016-01-25 15:06:00+00', E'2016-01-27 23:59:59+00', NULL, E'2016-01-25 15:06:27.474+00', E'2016-01-26 16:48:19.693+00');
37INSERT INTO "data" ("id", "hash", "source", "isFiltered", "campaignId", "data", "meta", "modifiedReason", "createdAt", "updatedAt") VALUES
38 (1, E'dHdpdHRlci02OTE5MTQ3ODcwNjA1ODQ0NDg=', E'a', E'false', 1, E'{}', E'{"profile":{"url":"xxx","image":"xxx","username":"xxx","name":"xxx","createdAt":"2015-10-05T10:30:11.000Z"},"posts":{"total":32,"perDay":0},"friends":0,"favourites":0,"createdAt":"2016-01-26T09:25:15.000Z","matchedOn":{"hashtags":["GavinTest123"],"accounts":[]}}', NULL, E'2016-01-26 09:25:15.539+00', E'2016-01-26 09:25:15.539+00'),
39 (2, E'dHdpdHRlci02OTE5MjAwNDAwNTcyNzAyNzI=', E'a', E'false', 1, E'{}', E'{"profile":{"url":"xxx","image":"xxx","username":"xxx","name":"xxx","createdAt":"2015-10-05T10:30:11.000Z"},"posts":{"total":34,"perDay":0},"friends":0,"favourites":0,"createdAt":"2016-01-26T09:46:07.000Z","matchedOn":{"hashtags":["GavinTest123"],"accounts":[]}}', NULL, E'2016-01-26 09:46:07.942+00', E'2016-01-26 09:46:07.942+00'),
40 (3, E'dHdpdHRlci02OTE5NjI4NjM5OTc1NTg3ODQ=', E'a', E'false', 1, E'{}', E'{"profile":{"url":"xxx","image":"xxx","username":"xxx","name":"xxx","createdAt":"2015-10-05T10:30:11.000Z"},"posts":{"total":36,"perDay":0},"friends":0,"favourites":0,"createdAt":"2016-01-26T12:36:17.000Z","matchedOn":{"hashtags":["GavinTest1234"],"accounts":[]}}', NULL, E'2016-01-26 12:36:17.724+00', E'2016-01-26 12:36:17.724+00');
41
42SELECT q."date",
43 q."hashtag",
44 Max(q."count") AS "count"
45FROM (
46 SELECT To_char(d::date, 'DD/MM/YYYY') AS "date",
47 0 AS "count",
48 c_h.hashtag::text AS "hashtag"
49 FROM generate_series('2016-01-20', '2016-01-26', '1 day'::interval) d
50 INNER JOIN "campaign" c
51 ON (
52 c."id" = 1)
53 INNER JOIN Json_array_elements(c.configuration->'query'->'hashtags') c_h(hashtag)
54 ON true
55 UNION ALL
56 SELECT To_char(cdi."createdAt"::date, 'DD/MM/YYYY') AS "date",
57 Count(cdi_h.hashtag::text) AS "count",
58 cdi_h.hashtag::text AS "hashtag"
59 FROM "data" cdi
60 INNER JOIN "campaign" c
61 ON (
62 c."id" = cdi."campaignId")
63 INNER JOIN json_array_elements(c.configuration->'query'->'hashtags') c_h(hashtag)
64 ON true
65 INNER JOIN json_array_elements(cdi.meta->'matchedOn'->'hashtags') cdi_h(hashtag)
66 ON (
67 c_h.hashtag::text = cdi_h.hashtag::text)
68 WHERE c."id" = 1
69 AND (
70 cdi."createdAt"::date >= '2016-01-20'
71 AND cdi."createdAt"::date <= '2016-01-26')
72 GROUP BY to_char(cdi."createdAt":: date, 'DD/MM/YYYY'),
73 cdi_h.hashtag::text
74 ORDER BY "date" ASC,
75 "hashtag" ASC,
76 "count" ASC ) q
77GROUP BY q."date",
78 q."hashtag";
79
80SELECT to_char(day, 'DD/MM/YYYY') AS date
81 , hashtag
82 , count(d.*)::int AS count
83FROM (
84 campaign c
85CROSS JOIN json_array_elements_text(c.configuration#>'{query,hashtags}') ch(hashtag)
86CROSS JOIN (SELECT g::date AS day
87 FROM generate_series(timestamp '2016-01-20', '2016-01-26', interval '1 day') g) day
88 )
89NATURAL LEFT JOIN (
90 SELECT "createdAt"::date AS day, dh.hashtag
91 FROM data, json_array_elements_text(meta#>'{matchedOn,hashtags}') dh(hashtag)
92 WHERE "campaignId" = 1
93 AND "createdAt" >= '2016-01-20'
94 AND "createdAt" < '2016-01-27'
95 ) d
96WHERE c.id = 1
97GROUP BY day, hashtag
98ORDER BY day, hashtag, count;