Closed Toms1999 closed 4 months ago
pgsql: SELECT ods_day, modified, task_code, transaction_code, CAST(content AS JSON) ->> '$.eventType' AS eventType, CAST(content AS JSON) ->> '$.unionCodeResults' AS unionCodeResults FROM edw_f_zone.gugu_yf_transaction_detail_dd WHERE transaction_label = 'unioncodelist' AND CAST(ods_day AS DATE) = '20240505';
mysql: SELECT ods_day, modified, task_code, transaction_code, CAST(content AS JSON) ->> '$."$.eventType"' AS eventType, CAST(content AS JSON) ->> '$."$.unionCodeResults"' AS unionCodeResults FROM edw_f_zone.gugu_yf_transaction_detail_dd WHERE transaction_label = 'unioncodelist' AND CAST(ods_day AS DATE) = '20240505'
more,pgsql: SELECT SUBSTR(CAST((CAST('2024-04-15' AS DATE) + INTERVAL '1 month ago') AS varchar), 1,7);
mysql:
SELECT
SUBSTR(CAST((CAST('2024-04-15' AS DATE) + INTERVAL '1 month ago') AS varchar), 1,7);
pgsql:`select * from (select 1 as obs, substring((current_time - interval '16 min') ::text, 1, 2) as value , (substring(current_time::text, 1, 2)::integer) || '>点' as name union all select 2 as obs, unnest(array['00', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24']) as value , unnest(array['0点','1点','2点','3点','4点','5点','6点','7点','8点','9点','10点','11点','12点','13点','14点','15点','16点','17点','18点','19点','20点','21点','22点','23点','24点']) as name) a
order by 1;`
mysql:SELECT * FROM (SELECT 1 AS obs, SUBSTRING(CAST((CURRENT_TIME() - INTERVAL '16' MIN) AS STRING), 1, 2) AS value, (CAST(SUBSTRING(CAST(CURRENT_TIME() AS STRING), 1, 2) AS INT)) || '>点' AS name UNION ALL SELECT 2 AS obs, EXPLODE(ARRAY('00', '01', '02', '03', '04', '05', '06', '07', '08', '09', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24')) AS value, EXPLODE(ARRAY('0点', '1点', '2点', '3点', '4点', '5点', '6点', '7点', '8点', '9点', '10点', '11点', '12点', '13点', '14点', '15点', '16点', '17点', '18点', '19点', '20点', '21点', '22点', '23点', '24点')) AS name) AS a ORDER BY 1;
pgsql:
select t1.rc_month, t2.id::varchar||'-'::varchar||t2.code_value overdue_reason,count(distinct loan_id) cnt from sherlock.t_dwp_jxj_bp_collect_record_rucui_trans_base t1 left join sherlock.t_overdue_reason_code t2 on t1.overdue_reason::bigint = t2.id WHERE t1.overdue_reason:: bigint not in (0 , 1 , 2 , 27) and rc_month is not null and ods_day<=to_char(due_date +interval '1 months' , 'YYYYmmdd') group by 1, 2
mysql:
SELECT t1.rc_month, CAST(t2.id AS VARCHAR) || CAST('-' AS VARCHAR) || t2.code_value AS overdue_reason, COUNT(DISTINCT loan_id) AS cnt FROM sherlock.t_dwp_jxj_bp_collect_record_rucui_trans_base AS t1 LEFT JOIN sherlock.t_overdue_reason_code AS t2 ON CAST(t1.overdue_reason AS BIGINT) = t2.id WHERE NOT CAST(t1.overdue_reason AS BIGINT) IN (0, 1, 2, 27) AND NOT rc_month IS NULL AND ods_day <= DATE_FORMAT(due_date + INTERVAL '1' MONTHS, '%Ymmdd') GROUP BY 1, 2;
more,
postgres:
select before_first_due_date::date + ((installment_count::int - 1) || ' month')::interval as clear_date from edw_dws.dws_unionloan_zhanqi_loan_df;
mysql:
SELECT CAST(before_first_due_date AS DATE) + CAST(((CAST(installment_count AS INT) - 1) || ' month') AS INTERVAL) AS clear_date FROM edw_dws.dws_unionloan_zhanqi_loan_df;
Before you file an issue
parse_one(sql, read="spark")
ast.sql(dialect="duckdb")
Fully reproducible code snippet Please include a fully reproducible code snippet or the input sql, dialect, and expected output.
Official Documentation Please include links to official SQL documentation related to your issue.