search

SiAce / pokemon-database

https://pokemon-siace.netlify.app
1 stars 0 forks source link

Query for Pokemon Encounters #3

Closed zete-dai-glidewell closed 2 months ago

zete-dai-glidewell commented 2 months ago 
SELECT
  encounter_id,
  encounters.pokemon_id,
  encounters.version_id,
  encounters.location_area_id,
  coalesce(
    location_area_prose.name,
    concat_ws(
      ' ',
      coalesce(location_names.name, locations.identifier),
      location_areas.identifier
    )
  ) as location,
  coalesce(
    encounter_method_prose.name,
    encounter_methods.identifier
  ) as encounter_method,
  encounter_condition_value_prose.name as encounter_condition,
  min_level,
  max_level
FROM
  encounters
  JOIN encounter_slots on encounter_slots.id = encounters.encounter_slot_id
  JOIN encounter_methods on encounter_methods.id = encounter_slots.encounter_method_id
  LEFT JOIN encounter_method_prose on encounter_method_prose.encounter_method_id = encounter_slots.encounter_method_id
  and encounter_method_prose.local_language_id = 9
  JOIN location_areas on location_areas.id = encounters.location_area_id
  JOIN locations on locations.id = location_areas.location_id
  LEFT JOIN location_names on location_names.location_id = location_areas.location_id
  AND location_names.local_language_id = 9
  LEFT JOIN location_area_prose on location_area_prose.location_area_id = encounters.location_area_id
  AND location_area_prose.local_language_id = 9
  LEFT JOIN encounter_condition_value_map on encounter_condition_value_map.encounter_id = encounters.id
  left JOIN encounter_condition_value_prose on encounter_condition_value_prose.encounter_condition_value_id = encounter_condition_value_map.encounter_condition_value_id
  and encounter_condition_value_prose.local_language_id = 9
order by
  pokemon_id,
  encounters.location_area_id,
  version_id
encounter_id    pokemon_id  version_id  location_area_id    location    encounter_method    encounter_condition min_level   max_level
    1   3   281 Cerulean City   Receive as a gift       10  10
    1   1   285 Pallet Town Receive as a gift       5   5
    1   2   285 Pallet Town Receive as a gift       5   5
    1   10  285 Pallet Town Receive as a gift       5   5
    1   11  285 Pallet Town Receive as a gift       5   5
50400   1   15  285 Pallet Town Receive as a gift   Defeat Red  5   5
50401   1   16  285 Pallet Town Receive as a gift   Defeat Red  5   5
    1   23  779 Lumiose City    Receive as a gift       10  10
    1   24  779 Lumiose City    Receive as a gift       10  10
    1   29  1040    Route 2 main    island-scan     10  10
    1   30  1040    Route 2 main    island-scan     10  10
    4   1   285 Pallet Town Receive as a gift       5   5
    4   2   285 Pallet Town Receive as a gift       5   5
    4   10  285 Pallet Town Receive as a gift       5   5
    4   11  285 Pallet Town Receive as a gift       5   5
50402   4   15  285 Pallet Town Receive as a gift   Defeat Red  5   5
50403   4   16  285 Pallet Town Receive as a gift   Defeat Red  5   5
    4   3   314 Road 24 Receive as a gift       10  10
    4   23  779 Lumiose City    Receive as a gift       10  10
    4   24  779 Lumiose City    Receive as a gift       10  10
    4   29  1043    Route 3 main    island-scan     12  12
    4   30  1043    Route 3 main    island-scan     12  12
    7   3   282 Vermilion City  Receive as a gift       10  10
    7   1   285 Pallet Town Receive as a gift       5   5
    7   2   285 Pallet Town Receive as a gift       5   5
    7   10  285 Pallet Town Receive as a gift       5   5
    7   11  285 Pallet Town Receive as a gift       5   5
50404   7   15  285 Pallet Town Receive as a gift   Defeat Red  5   5
50405   7   16  285 Pallet Town Receive as a gift   Defeat Red  5   5
    7   23  779 Lumiose City    Receive as a gift       10  10
    7   24  779 Lumiose City    Receive as a gift       10  10
    7   29  1132    Seaward Cave    island-scan     12  12
    7   30  1132    Seaward Cave    island-scan     12  12
9537    10  14  9   Eterna Forest   Walking in tall grass or a cave FireRed in slot 2   13  13
3264    10  12  144 Road 204 (south, towards Jubilife City) Walking in tall grass or a cave FireRed in slot 2   6   6
3270    10  12  144 Road 204 (south, towards Jubilife City) Walking in tall grass or a cave FireRed in slot 2   6   6
7935    10  13  144 Road 204 (south, towards Jubilife City) Walking in tall grass or a cave FireRed in slot 2   6   6
7941    10  13  144 Road 204 (south, towards Jubilife City) Walking in tall grass or a cave FireRed in slot 2   6   6
12635   10  14  144 Road 204 (south, towards Jubilife City) Walking in tall grass or a cave FireRed in slot 2   5   5
12641   10  14  144 Road 204 (south, towards Jubilife City) Walking in tall grass or a cave FireRed in slot 2   5   5
12683   10  14  145 Road 204 (north, towards Floaroma Town) Walking in tall grass or a cave FireRed in slot 2   10  10
12689   10  14  145 Road 204 (north, towards Floaroma Town) Walking in tall grass or a cave FireRed in slot 2   10  10
51281   10  4   187 Road 30 Walking in tall grass or a cave During the day  3   3
51284   10  4   187 Road 30 Walking in tall grass or a cave During the day  4   4
51288   10  4   187 Road 30 Walking in tall grass or a cave In the morning  3   3
51289   10  4   187 Road 30 Walking in tall grass or a cave In the morning  4   4
56274   10  6   187 Road 30 Walking in tall grass or a cave During the day  3   3
56275   10  6   187 Road 30 Walking in tall grass or a cave During the day  4   4
56281   10  6   187 Road 30 Walking in tall grass or a cave In the morning  3   3
56282   10  6   187 Road 30 Walking in tall grass or a cave In the morning  4   4
14083   10  15  187 Road 30 Walking in tall grass or a cave In the morning  3   3
14084   10  15  187 Road 30 Walking in tall grass or a cave During the day  3   3
14092   10  15  187 Road 30 Walking in tall grass or a cave In the morning  3   3
14092   10  15  187 Road 30 Walking in tall grass or a cave Radio off   3   3
14093   10  15  187 Road 30 Walking in tall grass or a cave During the day  3   3
14093   10  15  187 Road 30 Walking in tall grass or a cave Radio off   3   3
14097   10  15  187 Road 30 Walking in tall grass or a cave In the morning  4   4
14097   10  15  187 Road 30 Walking in tall grass or a cave Radio off   4   4
14102   10  15  187 Road 30 Walking in tall grass or a cave In the morning  4   4
14102   10  15  187 Road 30 Walking in tall grass or a cave Radio off   4   4
14114   10  15  187 Road 30 Walking in tall grass or a cave During the day  4   4
14120   10  15  187 Road 30 Walking in tall grass or a cave During the day  4   4
51302   10  4   188 Road 31 Walking in tall grass or a cave During the day  4   4
51305   10  4   188 Road 31 Walking in tall grass or a cave During the day  5   5
51309   10  4   188 Road 31 Walking in tall grass or a cave In the morning  4   4
51312   10  4   188 Road 31 Walking in tall grass or a cave In the morning  5   5
56295   10  6   188 Road 31 Walking in tall grass or a cave During the day  4   4
56296   10  6   188 Road 31 Walking in tall grass or a cave During the day  5   5
56302   10  6   188 Road 31 Walking in tall grass or a cave In the morning  4   4
56303   10  6   188 Road 31 Walking in tall grass or a cave In the morning  5   5
14154   10  15  188 Road 31 Walking in tall grass or a cave In the morning  4   4
14155   10  15  188 Road 31 Walking in tall grass or a cave During the day  4   4
14163   10  15  188 Road 31 Walking in tall grass or a cave In the morning  4   4
14163   10  15  188 Road 31 Walking in tall grass or a cave Radio off   4   4
14164   10  15  188 Road 31 Walking in tall grass or a cave During the day  4   4
14164   10  15  188 Road 31 Walking in tall grass or a cave Radio off   4   4
14180   10  15  188 Road 31 Walking in tall grass or a cave In the morning  5   5
14181   10  15  188 Road 31 Walking in tall grass or a cave During the day  5   5
14186   10  15  188 Road 31 Walking in tall grass or a cave In the morning  5   5
14187   10  15  188 Road 31 Walking in tall grass or a cave During the day  5   5
50566   10  4   204 Ilex Forest Walking in tall grass or a cave During the day  5   5
50567   10  4   204 Ilex Forest Walking in tall grass or a cave During the day  6   6
50573   10  4   204 Ilex Forest Walking in tall grass or a cave In the morning  5   5
50575   10  4   204 Ilex Forest Walking in tall grass or a cave In the morning  5   5
    10  4   204 Ilex Forest Headbutting a low encounter rate tree       10  10
    10  4   204 Ilex Forest Headbutting a low encounter rate tree       10  10
    10  4   204 Ilex Forest Headbutting a normal encounter rate tree        10  10
    10  4   204 Ilex Forest Headbutting a normal encounter rate tree        10  10
    10  4   204 Ilex Forest Headbutting a high encounter rate tree      10  10
55370   10  6   204 Ilex Forest Walking in tall grass or a cave During the day  5   5
55377   10  6   204 Ilex Forest Walking in tall grass or a cave In the morning  5   5
    10  6   204 Ilex Forest Headbutting a high encounter rate tree      10  10
14759   10  15  204 Ilex Forest Walking in tall grass or a cave In the morning  5   5
14760   10  15  204 Ilex Forest Walking in tall grass or a cave During the day  5   5
14764   10  15  204 Ilex Forest Walking in tall grass or a cave During the day  6   6
14767   10  15  204 Ilex Forest Walking in tall grass or a cave In the morning  5   5
14767   10  15  204 Ilex Forest Walking in tall grass or a cave Radio off   5   5
14768   10  15  204 Ilex Forest Walking in tall grass or a cave During the day  5   5
14768   10  15  204 Ilex Forest Walking in tall grass or a cave Radio off   5   5
14773   10  15  204 Ilex Forest Walking in tall grass or a cave During the day  6   6

One encounter_id could have multiple encounter_condition_value_id:

SELECT
  *
from
  (
    select
      encounter_id,
      encounter_condition_value_id,
      count(*) OVER (PARTITION by encounter_id) as c
    FROM
      encounter_condition_value_map
  ) t
WHERE
  t.c > 1
  order by t.c desc

limit 20
encounter_id    encounter_condition_value_id    c
62249   44  3
62249   42  3
62249   35  3
62250   45  3
62250   42  3
62250   35  3
14014   3   2
14014   14  2
14015   4   2
14015   14  2
14016   5   2
14016   14  2
14019   3   2
14019   14  2
14020   4   2
14020   14  2
14021   5   2
14021   14  2
14024   3   2
14024   14  2

Query to combine encounter condition values:

CREATE TABLE JoinedEncounter as
SELECT
  encounters.id,
  encounters.pokemon_id,
  encounters.version_id,
  encounters.location_area_id,
  encounters.encounter_slot_id,
  coalesce(
    location_area_prose.name,
    concat_ws(
      ' ',
      coalesce(location_names.name, locations.identifier),
      location_areas.identifier
    )
  ) as location,
  coalesce(
    encounter_method_prose.name,
    encounter_methods.identifier
  ) as encounter_method,
  (
    SELECT
      json_group_array(encounter_condition_value_prose.name)
    FROM
      encounter_condition_value_map
      JOIN encounter_condition_value_prose on encounter_condition_value_prose.encounter_condition_value_id = encounter_condition_value_map.encounter_condition_value_id
      AND encounter_condition_value_prose.local_language_id = 9
    WHERE
      encounter_condition_value_map.encounter_id = encounters.id
  ) as encounter_conditions,
  min_level,
  max_level
FROM
  encounters
  JOIN encounter_slots on encounter_slots.id = encounters.encounter_slot_id
  JOIN encounter_methods on encounter_methods.id = encounter_slots.encounter_method_id
  LEFT JOIN encounter_method_prose on encounter_method_prose.encounter_method_id = encounter_slots.encounter_method_id
  and encounter_method_prose.local_language_id = 9
  JOIN location_areas on location_areas.id = encounters.location_area_id
  JOIN locations on locations.id = location_areas.location_id
  LEFT JOIN location_names on location_names.location_id = location_areas.location_id
  AND location_names.local_language_id = 9
  LEFT JOIN location_area_prose on location_area_prose.location_area_id = encounters.location_area_id
  AND location_area_prose.local_language_id = 9
order by
  pokemon_id,
  encounters.location_area_id,
  version_id;
id  pokemon_id  version_id  location_area_id    encounter_slot_id   location    encounter_method    encounter_conditions    min_level   max_level
50297   1   3   281 491 Cerulean City   Receive as a gift   []  10  10
50290   1   1   285 490 Pallet Town Receive as a gift   []  5   5
50291   1   2   285 490 Pallet Town Receive as a gift   []  5   5
50355   1   10  285 496 Pallet Town Receive as a gift   []  5   5
50356   1   11  285 496 Pallet Town Receive as a gift   []  5   5
50400   1   15  285 499 Pallet Town Receive as a gift   ["Defeat Red"]  5   5
50401   1   16  285 499 Pallet Town Receive as a gift   ["Defeat Red"]  5   5
50450   1   23  779 502 Lumiose City    Receive as a gift   []  10  10
50451   1   24  779 502 Lumiose City    Receive as a gift   []  10  10
63135   1   29  1040    723 Route 2 main    island-scan []  10  10
63133   1   30  1040    723 Route 2 main    island-scan []  10  10
50292   4   1   285 490 Pallet Town Receive as a gift   []  5   5
50293   4   2   285 490 Pallet Town Receive as a gift   []  5   5
50357   4   10  285 496 Pallet Town Receive as a gift   []  5   5
50358   4   11  285 496 Pallet Town Receive as a gift   []  5   5
50402   4   15  285 499 Pallet Town Receive as a gift   ["Defeat Red"]  5   5
50403   4   16  285 499 Pallet Town Receive as a gift   ["Defeat Red"]  5   5
50298   4   3   314 491 Road 24 Receive as a gift   []  10  10
50452   4   23  779 502 Lumiose City    Receive as a gift   []  10  10
50453   4   24  779 502 Lumiose City    Receive as a gift   []  10  10

JoinedEncounter.csv

Query to find duplicates in encounter table:

SELECT *
from
(SELECT
  id,
  pokemon_id,
  version_id,
  location,
  encounter_slot_id,
  encounter_method,
  encounter_conditions,
  min_level,
  max_level,
  count(*) OVER (
    PARTITION by pokemon_id,
    version_id,
    location,
    encounter_slot_id,
    encounter_method,
    encounter_conditions,
    min_level,
    max_level
  ) as c
FROM
  JoinedEncounter)
WHERE c > 1
order by c desc
id  pokemon_id  version_id  location    encounter_slot_id   encounter_method    encounter_conditions    min_level   max_level   c
62350   100 4   Team Rocket HQ  563 Static encounter, only one chance   []  23  23  8
62351   100 4   Team Rocket HQ  563 Static encounter, only one chance   []  23  23  8
62352   100 4   Team Rocket HQ  563 Static encounter, only one chance   []  23  23  8
62353   100 4   Team Rocket HQ  563 Static encounter, only one chance   []  23  23  8
62354   100 4   Team Rocket HQ  563 Static encounter, only one chance   []  23  23  8
62355   100 4   Team Rocket HQ  563 Static encounter, only one chance   []  23  23  8
62356   100 4   Team Rocket HQ  563 Static encounter, only one chance   []  23  23  8
62357   100 4   Team Rocket HQ  563 Static encounter, only one chance   []  23  23  8
62358   100 5   Team Rocket HQ  563 Static encounter, only one chance   []  23  23  8
62359   100 5   Team Rocket HQ  563 Static encounter, only one chance   []  23  23  8
62360   100 5   Team Rocket HQ  563 Static encounter, only one chance   []  23  23  8
62361   100 5   Team Rocket HQ  563 Static encounter, only one chance   []  23  23  8
62362   100 5   Team Rocket HQ  563 Static encounter, only one chance   []  23  23  8
62363   100 5   Team Rocket HQ  563 Static encounter, only one chance   []  23  23  8
62364   100 5   Team Rocket HQ  563 Static encounter, only one chance   []  23  23  8
62365   100 5   Team Rocket HQ  563 Static encounter, only one chance   []  23  23  8
62366   100 6   Team Rocket HQ  564 Static encounter, only one chance   []  23  23  8
62367   100 6   Team Rocket HQ  564 Static encounter, only one chance   []  23  23  8
62368   100 6   Team Rocket HQ  564 Static encounter, only one chance   []  23  23  8
62369   100 6   Team Rocket HQ  564 Static encounter, only one chance   []  23  23  8

I suspect it's because the encounter_condition_value_map is missing values for some encounters, so there is no encounter conditions for them.

SiAce commented 2 months ago

57c4e27df1bfec1ca8333f5cd977f052c76af76e ddd57a981c2dcb96341e98292ff03bc62d78f956