...
| Code Block |
|---|
mysql> SELECT user.name , time.month_name , COUNT(*) as "job count" FROM job_fact AS fact INNER JOIN user_dim AS user ON fact.user_sk = user.user_sk INNER JOIN time_dim AS time ON fact.time_sk=time.time_sk WHERE time.year = 2013 GROUP BY user.name , time.month ORDER BY user.name , time.month ; +---------------+------------+-----------+ | name | month_name | job count | +---------------+------------+-----------+ | Administrator | January | 4 | | bar | July | 1 | | foo | July | 2 | | foobar | July | 1 | | foobar | September | 2 | | fubar | July | 1 | | jburk | March | 1 | | jburk | May | 123 | | jburk | June | 24 | | jburk | July | 220 | | jburk | August | 140 | | jburk | September | 68 | | jburk | October | 560 | | jburk | November | 59 | | jburk | December | 4467 | | perforce | January | 128 | | perforce | February | 4 | | root | January | 31 | | root | February | 37 | | root | March | 7 | | root | April | 17 | | root | May | 7 | | root | June | 1 | | root | July | 11 | | root | September | 2 | | root | December | 2 | | shinya | January | 23 | | shinya | February | 69 | | shinya | March | 7 | | shinya | April | 20 | | shinya | May | 2 | +---------------+------------+-----------+ |
...
| Code Block |
|---|
mysql> SELECT user.name |
...
, time.year |
...
, time.month_name |
...
, status.status_char |
...
, COUNT(*) as "job count" |
...
FROM |
...
job_fact AS fact |
...
INNER JOIN |
...
user_dim AS user |
...
ON |
...
fact.user_sk=user.user_sk |
...
INNER JOIN |
...
time_dim AS time |
...
ON |
...
fact.time_sk=time.time_sk |
...
INNER JOIN |
...
jobstatus_dim as status |
...
ON |
...
fact.jobstatus_sk=status.jobstatus_sk |
...
WHERE |
...
time.year = 2013 |
...
GROUP BY |
...
user.name |
...
, time.month |
...
, status.status_int |
...
ORDER BY |
...
user.name |
...
, time.month |
...
,status.status_char |
...
; |
...
+---------------+------+------------+-------------+-----------+ |
...
| name | year | month_name | status_char | job count | |
...
+---------------+------+------------+-------------+-----------+ |
...
| Administrator | 2013 | January | failed | 4 | |
...
| bar | 2013 | July | complete | 1 | |
...
| foo | 2013 | July | complete | 2 | |
...
| foobar | 2013 | July | complete | 1 | |
...
| foobar | 2013 | September | complete | 2 | |
...
| fubar | 2013 | July | complete | 1 | |
...
| jburk | 2013 | March | complete | 1 | |
...
| jburk | 2013 | May | complete | 100 | |
...
| jburk | 2013 | May | failed | 11 | |
...
| jburk | 2013 | May | killed | 12 | |
...
| jburk | 2013 | June | complete | 4 | |
...
| jburk | 2013 | June | failed | 20 | |
...
| jburk | 2013 | July | complete | 134 | |
...
| jburk | 2013 | July | failed | 33 | |
...
| jburk | 2013 | July | killed | 53 | |
...
| jburk | 2013 | August | complete | 67 | |
...
| jburk | 2013 | August | failed | 25 | |
...
| jburk | 2013 | August | killed | 48 | |
...
| jburk | 2013 | September | complete | 38 | |
...
| jburk | 2013 | September | failed | 17 | |
...
| jburk | 2013 | September | killed | 13 | |
...
| jburk | 2013 | October | complete | 450 | |
...
| jburk | 2013 | October | failed | 61 | |
...
| jburk | 2013 | October | killed | 49 | |
...
| jburk | 2013 | November | complete | 38 | |
...
| jburk | 2013 | November | failed | 12 | |
...
| jburk | 2013 | November | killed | 9 | |
...
| jburk | 2013 | December | complete | 3812 | |
...
| jburk | 2013 | December | failed | 627 | |
...
| jburk | 2013 | December | killed | 28 | |
...
| perforce | 2013 | January | failed | 46 | |
...
| perforce | 2013 | January | killed | 82 | |
...
| perforce | 2013 | February | complete | 3 | |
...
| perforce | 2013 | February | killed | 1 | |
...
| root | 2013 | January | complete | 24 | |
...
| root | 2013 | January | failed | 6 | |
...
| root | 2013 | January | killed | 1 | |
...
| root | 2013 | February | complete | 34 | |
...
| root | 2013 | February | killed | 3 | |
...
| root | 2013 | March | complete | 7 | |
...
| root | 2013 | April | complete | 9 | |
...
| root | 2013 | April | failed | 4 | |
...
| root | 2013 | April | killed | 4 | |
...
| root | 2013 | May | complete | 6 | |
...
| root | 2013 | May | killed | 1 | |
...
| root | 2013 | June | complete | 1 | |
...
| root | 2013 | July | complete | 7 | |
...
| root | 2013 | July | failed | 1 | |
...
| root | 2013 | July | killed | 3 | |
...
| root | 2013 | September | complete | 1 | |
...
| root | 2013 | September | failed | 1 | |
...
| root | 2013 | December | complete | 2 | |
...
| shinya | 2013 | January | complete | 4 | |
...
| shinya | 2013 | January | failed | 7 | |
...
| shinya | 2013 | January | killed | 12 | |
...
| shinya | 2013 | February | complete | 60 | |
...
| shinya | 2013 | February | failed | 2 | |
...
| shinya | 2013 | February | killed | 7 | |
...
| shinya | 2013 | March | complete | 2 | |
...
| shinya | 2013 | March | failed | 2 | |
...
| shinya | 2013 | March | killed | 3 | |
...
| shinya | 2013 | April | complete | 16 | |
...
| shinya | 2013 | April | killed | 4 | |
...
| shinya | 2013 | May | killed | 2 | |
...
+---------------+------+------------+-------------+-----------+ |
...