PrepperPrepper Open Data Bank

CI_PCS20_01

Summary

This dataset is a view that combines the “Population and Basic Census” data from the national census (2020 edition) with data extracted from the municipal survey statistics table [CI_ST], the municipal statistics master [CI_ST_MST], the municipal survey master [CI_TB_MST], and the municipal master table [CI_MST].

SQL

【E_CI_PCS20_01】

  1. create or replace secure view PODB_CITY.BETA.E_CI_PCS20_01(
  2.                ESTATDB_STATS_FULL_ID COMMENT ‘ESTATDB_統計量コード|truestarが付与|『テーブルID』『表01コード』『全カテゴリコード』を結合して独自作成’,
  3.                CITY_CODE COMMENT ‘市区町村コード | 出典：e-Stat | 元データのカラム名はarea_code’,
  4.                STATS_YEAR COMMENT ‘調査年度| 出典：e-Stat| 元データのカラム名は時間軸(年次)’,
  5.                STATS_VALUE COMMENT ‘統計値 | 出典：e-Stat | 元データのカラム名はvalue’,
  6.                STATS_UNIT COMMENT ‘統計単位 | 出典：e-Stat | 元データのカラム名はunit’,
  7.                ESTATDB_STATS_FULL_NAME COMMENT ‘ESTATDB_統計量名|truestarが付与|『表01名』『全カテゴリ名』を結合して独自作成’,
  8.                ESTATDB_RESEARCH_TABLE_ID COMMENT ‘ESTATDB_調査テーブルID|truestarが付与|『調査ID』『テーブルID』を結合して独自作成(関数)’,
  9.                STATS_TAB01_CODE COMMENT ‘統計量_属性_表01コード| 出典：e-Stat|元データのカラム名はtab_code’,
  10.                STATS_TAB01_NAME COMMENT ‘統計量_属性_表01名| 出典：e-Stat|元データのカラム名は表章項目’,
  11.                STATS_CAT01_CODE COMMENT ‘統計量_属性_カテゴリ01コード| 出典：e-Stat|元データのカラム名はcat01_code’,
  12.                STATS_CAT01_NAME COMMENT ‘統計量_属性_カテゴリ01名| 出典：e-Stat’,
  13.                STATS_CAT02_CODE COMMENT ‘統計量_属性_カテゴリ02コード| 出典：e-Stat|元データのカラム名はcat02_code’,
  14.                STATS_CAT02_NAME COMMENT ‘統計量_属性_カテゴリ02名| 出典：e-Stat’,
  15.                STATS_CAT03_CODE COMMENT ‘統計量_属性_カテゴリ03コード| 出典：e-Stat|元データのカラム名はcat03_code’,
  16.                STATS_CAT03_NAME COMMENT ‘統計量_属性_カテゴリ03名| 出典：e-Stat’,
  17.                STATS_CAT04_CODE COMMENT ‘統計量_属性_カテゴリ04コード| 出典：e-Stat|元データのカラム名はcat04_code’,
  18.                STATS_CAT04_NAME COMMENT ‘統計量_属性_カテゴリ04名| 出典：e-Stat’,
  19.                ESTATDB_TABLE_NAME COMMENT ‘ESTATDB_テーブル名|『表番号』『表題』『統計表名』を結合して独自作成（関数）’,
  20.                CITY_NAME COMMENT ‘市区町村名|出典：e-Stat|元データのカラム名は全国，都道府県，市区町村（2000年市区町村含む）’
  21. ) COMMENT=’国勢調査人口等基本集計 | 2020年’
  22.  as select aa.*, research_master.ESTATDB_TABLE_NAME,city_master.CITY_NAME
  24. from
  25. (//条件を絞ったresearch_statsからすべて、stats_masterから使うデータを選択をする
  26.     select research_stats.*,
  27.            stats_master.//ESTATDB_STATS_FULL_ID,
  28.                         ESTATDB_STATS_FULL_NAME,
  29.                         ESTATDB_RESEARCH_TABLE_ID,
  30.                         STATS_TAB01_CODE,
  31.                         STATS_TAB01_NAME,
  32.                         STATS_CAT01_CODE,
  33.                         STATS_CAT01_NAME,
  34.                         STATS_CAT02_CODE,
  35.                     STATS_CAT02_NAME,
  36.                         STATS_CAT03_CODE,
  37.                     STATS_CAT03_NAME,
  38.                         STATS_CAT04_CODE,
  39.                     STATS_CAT04_NAME
  40.       
  41.     from
  42.     ( //(PODB_RESEARCH_TABLE_ID, 7) = ‘0112020’であるデータをPODB_CITY.BETA.CI_STからすべてを選択し、それをresearch_statsとする。
  43.         select * from PODB_CITY.BETA.CI_ST
  44.         where left(ESTATDB_STATS_FULL_ID, 7) = ‘0112020’
  45.     ) as research_stats
  46.         //research_statsとstats_masterをESTATDB_STATS_FULL_IDでleft join。このときのPODB_CITY.BETA.CI_ST_MSTはstats_masterにとしている。
  47.         //left joinの条件はresearch_stats.ESTATDB_STATS_FULL_ID =stats_master.ESTATDB_STATS_FULL_IDである。
  48.         //ここでのprimary keyはESTATDB_STATS_FULL_IDである。
  49.         //結合のprimary keyはmiroを参考にすること。
  50.     left join
  51.     PODB_CITY.BETA.CI_ST_MST as stats_master
  52.     on research_stats.ESTATDB_STATS_FULL_ID =stats_master.ESTATDB_STATS_FULL_ID
  53. //research_statsとstats_masterを条件で結合したものをaaとする。aaの部分は任意の名前で良い
  54. ) as aa
  56. //aaとresearch_masterをESTATDB_RESEARCH_TABLE_IDでleft joinする。このときPODB_CITY.BETA.CI_TB_MSTをresearch_masterとしている
  57. //left joinの条件はaa.ESTATDB_RESEARCH_TABLE_ID = research_master.ESTATDB_RESEARCH_TABLE_ID。
  58. //ここでのprimary keyはESTATDB_RESEARCH_TABLE_IDである。
  59. //結合のprimary keyはmiroを参考にすること。
  60. left join PODB_CITY.BETA.CI_TB_MST as research_master
  61. on aa.ESTATDB_RESEARCH_TABLE_ID = research_master.ESTATDB_RESEARCH_TABLE_ID
  63. //aa.CITY側のみのデータを必要とする
  64. //citymaster.CITY_CODEとaa.CITY_CODEをright joinする。
  65. //PODB_CITY.BETA.CI_MSTをcity_masterとしている。
  66. inner join PODB_CITY.BETA.CI_MST as city_master
  67. on city_master.CITY_CODE = aa.CITY_CODE
  69. //市区町村の統計量のみを確実に表示するために使用
  70. where NOT CITY_NAME=’null’;

【J_CI_PCS20_01】

  1. create or replace secure view PODB_CITY.BETA.J_CI_PCS20_01(
  2.                “ESTATDB_統計量コード” COMMENT ‘truestarが付与|『テーブルID』『表01コード』『全カテゴリコード』を結合して独自作成’,
  3.                “市区町村コード” COMMENT ‘出典：e-Stat|元データのカラム名はarea_code’,
  4.                “調査年度” COMMENT ‘出典：e-Stat| 元データのカラム名は時間軸(年次)’,
  5.                “統計値” COMMENT ‘出典：e-Stat|元データのカラム名はvalue’,
  6.                “統計単位” COMMENT ‘出典：e-Stat|元データのカラム名はunit’,
  7.                “ESTATDB_統計量名” COMMENT ‘truestarが付与|『表01名』『全カテゴリ名』を結合して独自作成’,
  8.                “ESTATDB_調査テーブルID” COMMENT ‘truestarが付与|『調査ID』『テーブルID』を結合して独自作成(関数)’,
  9.                “統計量_属性_表01コード” COMMENT ‘出典：e-Stat|元データのカラム名はtab_code’,
  10.                “統計量_属性_表01名” COMMENT ‘出典：e-Stat|元データのカラム名は表章項目’,
  11.                “統計量_属性_カテゴリ01コード” COMMENT ‘出典：e-Stat|元データのカラム名はcat01_code’,
  12.                “統計量_属性_カテゴリ01名” COMMENT ‘出典：e-Stat’,
  13.                “統計量_属性_カテゴリ02コード” COMMENT ‘出典：e-Stat|元データのカラム名はcat02_code’,
  14.                “統計量_属性_カテゴリ02名” COMMENT ‘出典：e-Stat’,
  15.                “統計量_属性_カテゴリ03コード” COMMENT ‘出典：e-Stat|元データのカラム名はcat03_code’,
  16.                “統計量_属性_カテゴリ03名” COMMENT ‘出典：e-Stat’,
  17.                “統計量_属性_カテゴリ04コード” COMMENT ‘出典：e-Stat|元データのカラム名はcat04_code’,
  18.                “統計量_属性_カテゴリ04名” COMMENT ‘出典：e-Stat’,
  19.                “ESTATDB_テーブル名” COMMENT ‘truestarが付与|『表番号』『表題』『統計表名』を結合して独自作成（関数）’,
  20.                “市区町村名” COMMENT ‘出典：e-Stat|元データのカラム名は全国，都道府県，市区町村（2000年市区町村含む）’
  21. ) COMMENT=’国勢調査人口等基本集計 | 2020年’
  22.  as select aa.*, research_master.ESTATDB_TABLE_NAME,city_master.CITY_NAME
  24. from
  25. (//条件を絞ったresearch_statsからすべて、stats_masterから使うデータを選択をする
  26.     select research_stats.*,
  27.            stats_master.//ESTATDB_STATS_FULL_ID,
  28.                         ESTATDB_STATS_FULL_NAME,
  29.                         ESTATDB_RESEARCH_TABLE_ID,
  30.                         STATS_TAB01_CODE,
  31.                         STATS_TAB01_NAME,
  32.                         STATS_CAT01_CODE,
  33.                         STATS_CAT01_NAME,
  34.                         STATS_CAT02_CODE,
  35.                     STATS_CAT02_NAME,
  36.                         STATS_CAT03_CODE,
  37.                     STATS_CAT03_NAME,
  38.                         STATS_CAT04_CODE,
  39.                     STATS_CAT04_NAME
  40.      from
  41.     ( //(PODB_RESEARCH_TABLE_ID, 7) = ‘0112020’であるデータをPODB_CITY_DATA.BETA.CI_STからすべてを選択し、それをresearch_statsとする。
  42.         select * from PODB_CITY.BETA.CI_ST
  43.         where left(ESTATDB_STATS_FULL_ID, 7) = ‘0112020’
  44.     ) as research_stats
  45.         //research_statsとstats_masterをESTATDB_STATS_FULL_IDでleft join。このときのPODB_CITY_DATA.DEV.CI_ST_MSTはstats_masterにとしている。
  46.         //left joinの条件はresearch_stats.ESTATDB_STATS_FULL_ID =stats_master.ESTATDB_STATS_FULL_IDである。
  47.         //ここでのprimary keyはESTATDB_STATS_FULL_IDである。
  48.         //結合のprimary keyはmiroを参考にすること。
  49.     left join
  50.     PODB_CITY.BETA.CI_ST_MST as stats_master
  51.     on research_stats.ESTATDB_STATS_FULL_ID =stats_master.ESTATDB_STATS_FULL_ID
  52. //research_statsとstats_masterを条件で結合したものをaaとする。aaの部分は任意の名前で良い
  53. ) as aa
  55. //aaとresearch_masterをESTATDB_RESEARCH_TABLE_IDでleft joinする。このときPODB_CITY_DATA.BETA.CI_TB_MSTをresearch_masterとしている
  56. //left joinの条件はaa.ESTATDB_RESEARCH_TABLE_ID = research_master.ESTATDB_RESEARCH_TABLE_ID。
  57. //ここでのprimary keyはESTATDB_RESEARCH_TABLE_IDである。
  58. //結合のprimary keyはmiroを参考にすること。
  59. left join PODB_CITY.BETA.CI_TB_MST as research_master
  60. on aa.ESTATDB_RESEARCH_TABLE_ID = research_master.ESTATDB_RESEARCH_TABLE_ID
  62. //aa.CITY側のみのデータを必要とする
  63. //citymaster.CITY_CODEとaa.CITY_CODEをright joinする。
  64. //PODB_CITY_DATA.BETA.CI_MSTをcity_masterとしている。
  65. inner join PODB_CITY.BETA.CI_MST as city_master
  66. on city_master.CITY_CODE = aa.CITY_CODE
  67. where NOT CITY_NAME=’null’;

Sample Data

Data Fields

ESTATDB_STATS_FULL_ID

 

CITY_CODE

 

STATS_YEAR

 

STATS_VALUE

 

STATS_UNIT

  

ESTATDB_STATS_FULL_NAME

 

ESTATDB_RESEARCH_TABLE_ID

 

STATS_TAB01_CODE

 

STATS_TAB01_NAME

 

STATS_CAT01_CODE

 

STATS_CAT01_NAME

 

STATS_CAT02_CODE

 

STATS_CAT02_NAME

 

STATS_CAT03_CODE

 

STATS_CAT03_NAME

 

STATS_CAT04_CODE

 

STATS_CAT04_NAME

 

ESTATDB_TABLE_NAME

 

CITY_NAME

 

Special Notes

Issue:

There are records where the ESTATDB_Statistics_full_name [ESTATDB_STATS_FULL_NAME] is the same, but the statistics value [STATS_VALUE] is different.

Reason for difference:

This is because the ESTATDB_Table_name [ESTATDB_TABLE_NAME] is different.

Details:

  • This is an example of table number 3-3 and table number 4-3-1.
ESTATDB_STATS_FULL_IDESTATDB_STATS_FULL_NAMESTATS_VALUECITY_NAMEESTATDB_TABLE_NAME
0112020009|202001|0|0|00|0人口|国籍総数|総数|総数|総数1,973,395札幌市3-3男女，年齢（5歳階級），出生の月，国籍総数か日本人別人口－全国，都道府県，市区町村
0112020010|202001|0|0|0|00人口|国籍総数|総数|総数|総数1,726,966札幌市4-3-1男女，年齢（5歳階級），配偶関係，国籍総数か日本人別人口（15歳以上）－全国，都道府県，市区町村

Example Case:

The statistics values, such as STATS_VALUE, are different despite the ESTATDB_STAT_FULL_NAME being the same.

The reason for the difference in statistics values is because Table Number 3-3 includes the total population, including those under 15 years old, while Table Number 4-3-1 includes the total population of those over 15 years old.

Note:

If only ESTATDB_STAT_FULL_NAME is specified to calculate the statistics value, it will result in the sum of the statistics value from two tables.

Solution:

When specifying ESTATDB_STAT_FULL_NAME to calculate the statistics value, please also specify ESTATDB_STAT_FULL_ID or ESTATDB_TABLE_NAME.