chenweihua commented on issue #10198:
URL: https://github.com/apache/seatunnel/issues/10198#issuecomment-3664119289
CAST(
ROUND(
CAST(CAST(`Longitude` AS STRING) AS DECIMAL(15,8)),
6
) AS STRING
) as longitude,
CAST(
ROUND(
CAST(CAST(`Latitude` AS STRING) AS DECIMAL(15,8)),
6
) AS STRING
) as latitude,
-- 检查CDC原始值的字符串表示
CAST(`data`.`Longitude` AS STRING) as cdc_longitude_string,
CAST(`data`.`Latitude` AS STRING) as cdc_latitude_string,
-- 检查CDC原始值的高精度DECIMAL
CAST(`data`.`Longitude` AS DECIMAL(20,10)) as
cdc_longitude_decimal_20_10,
CAST(`data`.`Latitude` AS DECIMAL(20,10)) as
cdc_latitude_decimal_20_10,
-- 从字符串重新转换为DECIMAL（绕过CDC可能的精度问题）
CAST(CAST(`data`.`Longitude` AS STRING) AS DECIMAL(20,10)) as
string_to_decimal_long,
CAST(CAST(`data`.`Latitude` AS STRING) AS DECIMAL(20,10)) as
string_to_decimal_lat,
-- 四舍五入的字符串转换
CAST(ROUND(CAST(CAST(`data`.`Longitude` AS STRING) AS DECIMAL(20,10)),
6) AS STRING) as final_longitude_string,
CAST(ROUND(CAST(CAST(`data`.`Latitude` AS STRING) AS DECIMAL(20,10)),
6) AS STRING) as final_latitude_string,
-- 检查是否CDC本身就有精度问题
CASE
WHEN CAST(`data`.`Longitude` AS STRING) = '126.752251' THEN
'CDC_CORRECT'
WHEN CAST(`data`.`Longitude` AS STRING) LIKE '126.752250%' THEN
'CDC_PRECISION_LOSS'
ELSE 'CDC_OTHER'
END as cdc_longitude_status,
CASE
WHEN CAST(`data`.`Latitude` AS STRING) = '44.916103' THEN
'CDC_CORRECT'
WHEN CAST(`data`.`Latitude` AS STRING) LIKE '44.916103%' THEN
'CDC_CORRECT_ROUND'
ELSE 'CDC_OTHER'
END as cdc_latitude_status,
-- 精度差异计算
ABS(CAST(`data`.`Longitude` AS DECIMAL(12,6)) - 126.752251) as
longitude_diff,
ABS(CAST(`data`.`Latitude` AS DECIMAL(12,6)) - 44.916103) as
latitude_diff,
做了这么多测试，在生成插入语句 的时候还是不正确，没办法，麻烦多指教
INSERT INTO "..."."public"."t_station"("id", "code", "name", "province_id",
"city_id", "county_id", "address", "cdc_longitude_original",
"cdc_latitude_original", "longitude", "latitude", "cdc_longitude_string",
"cdc_latitude_string", "cdc_longitude_decimal_20_10",
"cdc_latitude_decimal_20_10", "string_to_decimal_long",
"string_to_decimal_lat", "final_longitude_string", "final_latitude_string",
"cdc_longitude_status", "cdc_latitude_status", "longitude_diff",
"latitude_diff", "create_time", "status", "update_time", "area_id",
"area_name", "source_table") VALUES (('1'::int4), ('...'), ('...'),
('43'::int4), ('4309'::int4), ('430902'::int4), ('...'), ('126.75225'::real),
('44.916103'::real), ('126.752250'), ('44.916103'), ('126.75225'),
('44.916103'), ('126.7522500000'::numeric), ('44.9161030000'::numeric),
('126.7522500000'::numeric), ('44.9161030000'::numeric), ('126.752250'),
('44.916103'), ('CDC_OTHER'), ('CDC_CORRECT'), ('0.000001'::numeric),
('0.000000'::numeric), ('2017-10-02 0
0:00:00+08'), ('FROZEN'), ('2020-03-19 19:50:01+08'), ('0'::int4), (''),
('...')
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
