|
|
# Pro exporty
|
|
|
**Pro výzkum je nejlepší vytvořit speciální verzi hry <APP_VERSION>, podle které se dají data pak snadno vyfiltrovat.**
|
|
|
|
|
|
### Vybrat všechny uživatele a časy začátku her, na verzi hry <APP_VERSION> co se hráli od <UNIX_TIME_IN_MILISECONDS>
|
|
|
```
|
|
|
Vybrat všechny uživatele a časy začátku her, na verzi hry <APP_VERSION> co se hráli od <UNIX_TIME_IN_MILISECONDS>
|
|
|
```sql
|
|
|
select user_id, to_timestamp(start_time/1000) from "game" where application_version_name='<APP_VERSION>' and start_time > <UNIX_TIME_IN_MILISECONDS>;
|
|
|
```
|
|
|
### Totéž s použitím join na user
|
|
|
```
|
|
|
Totéž s použitím join na user
|
|
|
```sql
|
|
|
select "user".name as Prezdivka, "user".age as Vek, "game".user_rank as Hodnost, "game".game_number as Hra, "game".difficulty_number as Obtiznost_hry, to_timestamp("game".start_time/1000) as Zacatek_hry, to_timestamp("game".end_time/1000) as Konec_hry from "game" join "user" on "game".user_id="user".id where "game".application_version_name='<APP_VERSION>' and "game".start_time > 1662422400000 and "game".start_time < <UNIX_TIME_IN_MILISECONDS> order by "user".name, "game".game_number, "game".user_rank;
|
|
|
```
|
|
|
|
|
|
### Všechny odehrané hry, kde vyberu dle verze aplikace <APP_VERSION>, času začátku hraní a konce začátku hraní seřazeno podle jména uživatele, hry a hodnot v game_score
|
|
|
```
|
|
|
Všechny odehrané hry, kde vyberu dle verze aplikace <APP_VERSION>, času začátku hraní a konce začátku hraní seřazeno podle jména uživatele, hry a hodnot v game_score
|
|
|
```sql
|
|
|
select "user".name as Prezdivka, "user".age as Vek, "game".user_rank as Hodnost, "game".id as game_id, case when "game".game_number=1 then 'Lupici' when "game".game_number=2 then 'Pronasledovani' when "game".game_number=3 then 'Unos' when "game".game_number=4 then 'Hlidka' when "game".game_number=5 then 'Strelnice' when "game".game_number=6 then 'Potme' when "game".game_number=7 then 'Symboly' when "game".game_number=8 then 'Misto cinu' when "game".game_number=9 then 'Protokol' when "game".game_number=10 then 'Tajny kod' when "game".game_number=11 then 'Na stope' when "game".game_number=12 then 'Archiv' when "game".game_number=13 then 'Chyt zlodeje' else 'Neznamá hra' end as Hra, case when "game".difficulty_number=0 then 'tutorial' when "game".difficulty_number=1 then 'lehka' when "game".difficulty_number=2 then 'stredni' when "game".difficulty_number=3 then 'tezka' when "game".difficulty_number=4 then 'bonus' else 'Neznamá obtiznost' end as Obtiznost_hry, to_timestamp("game".start_time/1000) as Zacatek_hry, to_timestamp("game".end_time/1000) as Konec_hry, "game_score".key, "game_score".value, "game".application_version_name as Verze_hry from "game" join "user" on "game".user_id="user".id join "game_score" on "game".id="game_score".game_id where "game".application_version_name='<APP_VERSION>' and "game".start_time > <UNIX_TIME_IN_MILISECONDS> and "game".start_time < <UNIX_TIME_IN_MILISECONDS> order by "user".name, "game".game_number, "game_score".game_id;
|
|
|
```
|
|
|
|
|
|
### Exporty po jednotlivých hrách dle game_number v tabulce "game" s hodnotami z tabulky "game_score" - pro výpočet celkového skore
|
|
|
```
|
|
|
Exporty po jednotlivých hrách dle game_number v tabulce "game" s hodnotami z tabulky "game_score" - pro výpočet celkového skore
|
|
|
```sql
|
|
|
select "user".name as Prezdivka, "user".age as Vek, "game".user_rank as Hodnost, "game".id as game_id, case when "game".game_number=1 then 'Lupici' when "game".game_number=2 then 'Pronasledovani' when "game".game_number=3 then 'Unos' when "game".game_number=4 then 'Hlidka' when "game".game_number=5 then 'Strelnice' when "game".game_number=6 then 'Potme' when "game".game_number=7 then 'Symboly' when "game".game_number=8 then 'Misto cinu' when "game".game_number=9 then 'Protokol' when "game".game_number=10 then 'Tajny kod' when "game".game_number=11 then 'Na stope' when "game".game_number=12 then 'Archiv' when "game".game_number=13 then 'Chyt zlodeje' else 'Neznamá hra' end as Hra, case when "game".difficulty_number=0 then 'tutorial' when "game".difficulty_number=1 then 'lehka' when "game".difficulty_number=2 then 'stredni' when "game".difficulty_number=3 then 'tezka' when "game".difficulty_number=4 then 'bonus' else 'Neznamá obtiznost' end as Obtiznost_hry, to_timestamp("game".start_time/1000) as Zacatek_hry, to_timestamp("game".end_time/1000) as Konec_hry, "game_score".key, "game_score".value from "game" join "user" on "game".user_id="user".id join "game_score" on "game".id="game_score".game_id where "game".application_version_name='<APP_VERSION>' and "game".start_time > <UNIX_TIME_IN_MILISECONDS> and "game".start_time < <UNIX_TIME_IN_MILISECONDS> and "game".game_number=<GAME_NUMBER> order by "user".name, "game".game_number, "game_score".game_id;
|
|
|
```
|
|
|
### Exporty pro Pronásledování pro výpočet skore (počítá se z délky hraní hry mínus pauzy)
|
|
|
```
|
|
|
Exporty pro Pronásledování pro výpočet skore (počítá se z délky hraní hry mínus pauzy)
|
|
|
```sql
|
|
|
select "user".name as Prezdivka, "user".age as Vek, "game".user_rank as Hodnost, "game".id as game_id, case when "game".game_number=1 then 'Lupici' when "game".game_number=2 then 'Pronasledovani' when "game".game_number=3 then 'Unos' when "game".game_number=4 then 'Hlidka' when "game".game_number=5 then 'Strelnice' when "game".game_number=6 then 'Potme' when "game".game_number=7 then 'Symboly' when "game".game_number=8 then 'Misto cinu' when "game".game_number=9 then 'Protokol' when "game".game_number=10 then 'Tajny kod' when "game".game_number=11 then 'Na stope' when "game".game_number=12 then 'Archiv' when "game".game_number=13 then 'Chyt zlodeje' else 'Neznamá hra' end as Hra, case when "game".difficulty_number=0 then 'tutorial' when "game".difficulty_number=1 then 'lehka' when "game".difficulty_number=2 then 'stredni' when "game".difficulty_number=3 then 'tezka' when "game".difficulty_number=4 then 'bonus' else 'Neznamá obtiznost' end as Obtiznost_hry, to_timestamp("game".start_time/1000) as Zacatek_hry, to_timestamp("game".end_time/1000) as Konec_hry, (("game".end_time/1000)-("game".start_time/1000)) as Delka_hry, SUM(("game_pause".end_time/1000)-("game_pause".start_time/1000)) as celkova_delka_pauz, "game".application_version_name as Verze_hry from "game" join "game_pause" on "game".id="game_pause".game_id join "user" on "game".user_id="user".id where "game".application_version_name='<APP_VERSION>' and "game".start_time > <UNIX_TIME_IN_MILISECONDS> and "game".start_time < <UNIX_TIME_IN_MILISECONDS> and "game".game_number=2 group by "game".id, "user".id order by "user".name, "game".id;
|
|
|
```
|
|
|
|
|
|
|
|
|
### Export do .csv souboru
|
|
|
```
|
|
|
Export do .csv souboru
|
|
|
```sql
|
|
|
\copy (select ... <SELECT_PSQL_COMMAND>) To '/home/name/export.csv' With CSV DELIMITER ',' HEADER
|
|
|
```
|
|
|
|
|
|
### Přesun souboru ze serveru k sobě
|
|
|
```
|
|
|
Přesun souboru ze serveru k sobě
|
|
|
```sql
|
|
|
scp user@tablexia-server.labs.nic.cz:/home/<USERNAME>/export.csv /home/<USERNAME>/Documents/exports/
|
|
|
```
|
|
|
|
... | ... | |