join2.sql

SELECT
  id, t1.name, t1.model, body_name, color, engine, t1.grade, grade_seq, inspection, recycle,
  year_ym, start_ym, end_ym,
  country_name, series, person, height, length, width,
  t1.price AS used_price, t2.price AS new_price,

  large_area_name, shop_pref_name, shop_lat, shop_lng, shop_name,t1.shop_pref_code,
  maintenance, maintenance_fee,

  odd, odd_numerics, odd_unit,
  IF( substr(odd_unit, 0, 2) = 'km', odd_numerics, odd_numerics*10000 ) AS odd_km,

  warranty, warranty_distance, warranty_fee, warranty_length_kigen, warranty_length_kikan,
  IF( substr(warranty_length_kikan, length(warranty_length_kikan), 1) = '年',
      regexp_extract(warranty_length_kikan, '([0-9]*)(.*)', 1) * 12,
      regexp_extract(warranty_length_kikan, '([0-9]*)(.*)', 1)
  ) AS warranty_length_kikan_m,

  main_photo_l, `desc`
FROM
(
    SELECT id, name, model, code, price, recycle, body_name, color, engine, grade, inspection, maintenance_fee, shop_pref_name, shop_pref_code, shop_lat, shop_lng, shop_name, maintenance, warranty, warranty_distance, warranty_fee, warranty_length_kigen, warranty_length_kikan, year*100+1 AS year_ym, main_photo_l, `desc`,
    odd,
    CAST(regexp_extract(odd, '([\.0-9]*)(.*)', 1) AS FLOAT) AS odd_numerics,
    regexp_extract(odd, '([\.0-9]*)(.*)', 2) AS odd_unit
    FROM usedcar
) t1
JOIN
(
    SELECT TD_X_RANK(grade) AS grade_seq, grade, code, model, person, price, height, length, period, width, series ,start_ym, end_ym
    FROM
    (
      SELECT grade, person, price, code, model, height, length, period, width, series,
      CAST( substr(period, 0, 6) AS INT) AS start_ym,
      CAST(substr(period, 8, 6)  AS INT) AS end_ym
      FROM catalog
      ORDER BY grade
    ) tmp
    WHERE TD_X_RANK(grade) = 1
) t2
ON ( t1.grade = t2.grade AND t1.code = t2.code AND t1.model = t2.model )
JOIN
(
    SELECT country_name, name
    FROM brand_master
) t3
ON ( t1.name = t3.name )
JOIN
(
    SELECT code, large_area_name
    FROM pref_master
) t4
ON ( t1.shop_pref_code = t4.code )
WHERE start_ym <= year_ym AND year_ym <= end_ym