Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- | id | domainIP |
- | foo | 158.132.34.5 |
- | bob | 128.12.244.3 |
- | bob | 128.12.244.3 |
- | bob | 19.152.134.4 |
- | bob | 168.152.34.9 |
- | alice | 178.132.64.10 |
- | alice | 188.152.214.200 |
- | peter | 208.162.36.153 |
- | peter | 208.162.36.153 |
- | peter | 208.162.36.153 |
- | peter | 198.168.94.201 |
- SELECT
- `log`.`id`,
- `log`.`domainIP`,
- COUNT(`log`.`domainIP`) AS "Times",
- totalsTable.Totals,
- (COUNT(`log`.`domainIP`)/totalsTable.Totals)*100 AS "Percentage"
- FROM `log`
- JOIN
- (
- SELECT
- `id`,
- COUNT(`domainIP`) AS Totals
- FROM `log` GROUP BY `id`
- ) AS totalsTable
- ON (`log`.`id` = totalsTable.`id`)
- GROUP BY `log`.`domainIP` ORDER BY `log`.`id` ASC, "Percentage" DESC
- | id | domainIP | Times | Totals | Percentage
- | foo | 158.132.34.5 | 1 | 1 | 100
- | bob | 128.12.244.3 | 2 | 4 | 50
- | bob | 19.152.134.4 | 1 | 4 | 25
- | bob | 168.152.34.9 | 1 | 4 | 25
- | alice | 178.132.64.10 | 1 | 2 | 50
- | alice | 188.152.214.200 | 1 | 2 | 50
- | peter | 208.162.36.153 | 3 | 4 | 75
- | peter | 198.168.94.201 | 1 | 4 | 25
- CREATE TABLE `log` (
- `id` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
- `eDate` datetime DEFAULT NULL,
- `domainIP` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL,
- `event` varchar(150) COLLATE utf8_unicode_ci DEFAULT NULL
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
- ALTER TABLE `log`
- ADD UNIQUE KEY `logUnique` (`id`,`eDate`,`event`),
- ADD KEY `eDate` (`eDate`),
- ADD KEY `id` (`id`,`eDate`),
- ADD KEY `event` (`id`,`eDate`,`event`);
- id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra
- 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 100 | Using where; Using temporary; Using filesort
- 1 | PRIMARY | log | ref | logUnique,id,event | logUnique | 453 | totalsTable.id | 1 |
- 2 | DERIVED | log | index | NULL | id | 459 | NULL | 100 |
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement