Create a gist now

Instantly share code, notes, and snippets.

@doryokujin /join2.sql Secret
Created Jan 29, 2014

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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment