diff options
Diffstat (limited to 'db/sql/views.sql')
-rw-r--r-- | db/sql/views.sql | 27 |
1 files changed, 27 insertions, 0 deletions
diff --git a/db/sql/views.sql b/db/sql/views.sql new file mode 100644 index 0000000..c56286d --- /dev/null +++ b/db/sql/views.sql | |||
@@ -0,0 +1,27 @@ | |||
1 | CREATE VIEW IF NOT EXISTS aggregated_stats AS | ||
2 | SELECT u.uuid, SUM(s.quantity * s.size) from Statistics s INNER JOIN Users u ON u.id = s.user_id INNER JOIN Preferences p ON p.user_id = u.id INNER JOIN Size s ON s.id = p.size_id; | ||
3 | |||
4 | CREATE VIEW IF NOT EXISTS `DailyUserStatistics` AS | ||
5 | SELECT users.name, IFNULL(SUM(statistics.quantity), 0) as total, preferences.color as color | ||
6 | FROM users | ||
7 | LEFT JOIN statistics ON users.id = statistics.user_id AND DATE(statistics.date) = DATE('now', '-1 day') | ||
8 | LEFT JOIN preferences ON users.id = preferences.user_id | ||
9 | GROUP BY users.name; | ||
10 | |||
11 | |||
12 | CREATE VIEW IF NOT EXISTS `WeeklyStatisticsView` AS | ||
13 | WITH DateSequence(Dates) AS | ||
14 | ( | ||
15 | SELECT Date(CURRENT_DATE, '-7 day') | ||
16 | UNION ALL | ||
17 | SELECT Date(Dates, '+1 day') | ||
18 | FROM DateSequence | ||
19 | WHERE Date(Dates, '+1 day') < Date(CURRENT_DATE) | ||
20 | ) | ||
21 | SELECT DateSequence.Dates as 'date', | ||
22 | IFNULL(SUM(statistics.quantity), 0) AS 'total' | ||
23 | FROM DateSequence | ||
24 | LEFT JOIN statistics | ||
25 | ON Date(statistics.date) = DateSequence.Dates | ||
26 | GROUP BY DateSequence.Dates | ||
27 | ORDER BY DateSequence.Dates; \ No newline at end of file | ||