|
|
|
# Export dat z SQLite databaze na devpubu
|
|
|
|
|
|
|
|
Pred volanim musime nastavit vystup:
|
|
|
|
```sql
|
|
|
|
.mode csv
|
|
|
|
.header on
|
|
|
|
.separator ';'
|
|
|
|
.out 'stats.csv'
|
|
|
|
```
|
|
|
|
|
|
|
|
Dotaz:
|
|
|
|
```sql
|
|
|
|
SELECT strftime('%d.%m.%Y', played.start_time) as Datum, strftime('%H:%M:%S', played.start_time) as Zacatek, CASE WHEN (strftime('%s', played.end_time) - strftime('%s', played.start_time) < 0) THEN 'nedohrano' ELSE cast(strftime('%s', played.end_time) - strftime('%s', played.start_time) as TEXT) || 's' END as Doba, game.name as Hra, played.difficulty as Obtiznost, played.counter as Counter, played.extra_int_1 as extra_int_1, played.extra_int_2 as extra_int_2, played.extra_int_3 as extra_int_3, played.extra_int_4 as extra_int_4, played.stars_number as stars_number, played.game_pauses as Pauzy, user.nick_name as Nick, CASE WHEN user.is_male = 1 THEN 'Muž' ELSE 'Žena' END as Pohlavi, user.age as Vek, user.device_id as device_id, user.remote_id as user_id FROM stats_gameplayed played JOIN stats_upload upload ON upload.id = played.upload_id JOIN stats_game game ON played.game_id = game.id JOIN stats_user user ON user.id = played.user_id;
|
|
|
|
```
|
|
|
|
|
|
|
|
pro omezeni na konkretni zarizeni pridame:
|
|
|
|
```sql
|
|
|
|
WHERE upload.device_id IN ('D7OKCT001129', 'D7OKCT001130','D7OKCT001197','D7OKCT001151','D7OKCT001153')
|
|
|
|
```
|
|
|
|
omezeni na urcity interval
|
|
|
|
```sql
|
|
|
|
WHERE played.start_time > '2013-11-26 10:00:00' AND played.start_time < '2013-11-26 13:00:00'
|
|
|
|
``` |
|
|
\ No newline at end of file |
