PrepperPrepper Open Data Bank

ER DIAGRAM

ER DIAGRAM

To make it easier to understand the correlation between each table and view, we prepared an ER diagram that shows the relationship between each table.

*Click here to go to an external site (Miro) where you can enlarge or reduce the image.

SQL for statistics of Minato-ku, Tokyo

  1. SELECT
  2.     //市区町村マスタから都道府県コード、都道府県名、市区町村コード、市区町村名を選択
  3.     PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_MST.PREF_CODE,
  4.     PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_MST.PREF_NAME,
  5.     PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_MST.CITY_CODE AS CITY_CODE_MASTER, //市区町村マスタの CITY_CODE
  6.     PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_MST.CITY_NAME,
  8.     //統計テーブルから統計テーブル名を選択
  9.     PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_TB_MST.ESTATDB_TABLE_NAME,
  11.     //統計データマスターから統計データに関する情報を選択
  12.     Stats_data_master.ESTATDB_STATS_FULL_ID,
  13.     Stats_data_master.STATS_YEAR,
  14.     Stats_data_master.STATS_VALUE,
  15.     Stats_data_master.STATS_UNIT,
  17.     //統計マスタからの追加情報
  18.     Stats_data_master.ESTATDB_STATS_FULL_NAME,
  19.     Stats_data_master.ESTATDB_RESEARCH_TABLE_ID,
  20.     Stats_data_master.STATS_TAB01_CODE,
  21.     Stats_data_master.STATS_TAB01_NAME,
  22.     Stats_data_master.STATS_CAT01_CODE,
  23.     Stats_data_master.STATS_CAT01_NAME,
  24.     Stats_data_master.STATS_CAT02_CODE,
  25.     Stats_data_master.STATS_CAT02_NAME,
  26.     Stats_data_master.STATS_CAT03_CODE,
  27.     Stats_data_master.STATS_CAT03_NAME,
  28.     Stats_data_master.STATS_CAT04_CODE,
  29.     Stats_data_master.STATS_CAT04_NAME,
  30.     Stats_data_master.STATS_CAT05_CODE,
  31.     Stats_data_master.STATS_CAT05_NAME
  32. FROM (
  33.     //統計データと統計マスタを結合
  34.     //PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST は実際の統計データ
  35.     //PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST_MST は統計マスタ情報
  36.     SELECT
  37.         //統計データテーブルから必要なカラムを選択
  38.         PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST.ESTATDB_STATS_FULL_ID,
  39.         PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST.CITY_CODE,
  40.         PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST.STATS_YEAR,
  41.         PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST.STATS_VALUE,
  42.         PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST.STATS_UNIT,
  44.         //統計マスタ情報を選択
  45.         PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST_MST.ESTATDB_STATS_FULL_NAME,
  46.         PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST_MST.ESTATDB_RESEARCH_TABLE_ID,
  47.         PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST_MST.STATS_TAB01_CODE,
  48.         PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST_MST.STATS_TAB01_NAME,
  49.         PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST_MST.STATS_CAT01_CODE,
  50.         PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST_MST.STATS_CAT01_NAME,
  51.         PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST_MST.STATS_CAT02_CODE,
  52.         PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST_MST.STATS_CAT02_NAME,
  53.         PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST_MST.STATS_CAT03_CODE,
  54.         PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST_MST.STATS_CAT03_NAME,
  55.         PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST_MST.STATS_CAT04_CODE,
  56.         PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST_MST.STATS_CAT04_NAME,
  57.         PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST_MST.STATS_CAT05_CODE,
  58.         PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST_MST.STATS_CAT05_NAME
  60.     //統計データと統計マスタをESTATDB_STATS_FULL_IDで結合
  61.     FROM PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST
  62.     INNER JOIN PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST_MST
  63.         ON PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST.ESTATDB_STATS_FULL_ID = PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_ST_MST.ESTATDB_STATS_FULL_ID
  64. ) AS Stats_data_master
  66. //統計データと研究テーブルをESTATDB_RESEARCH_TABLE_IDで結合
  67. LEFT JOIN PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_TB_MST
  68.     ON Stats_data_master.ESTATDB_RESEARCH_TABLE_ID = PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_TB_MST.ESTATDB_RESEARCH_TABLE_ID
  70. //市区町村情報と統計データをCITY_CODEで結合
  71. INNER JOIN PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_MST
  72.     ON PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_MST.CITY_CODE = Stats_data_master.CITY_CODE
  74. //東京都港区に絞り込む
  75. WHERE PREPPER_OPEN_DATA_BANK__JAPANESE_CITY_DATA.PODB.CI_MST.PREF_CODE = '13' //東京都
  76.   AND Stats_data_master.CITY_CODE = '13103'; //港区