如何将Postgres代码转换为雪花？

Question

我很难将ff Postgres代码转换为雪花码。有人能帮忙吗？

WITH sitel_users as (
          select
            user_id
          from validation_rep vr
          where true
            and (vr.feature_flags ->> 'is_bogota')::bool is true
        ), call_grade as (
          select
              aoc.user_id
            , (aocg.created_at at time zone 'z' at time zone 'us/central')::date as call_grade_date
            , avg(aocg.score) call_grade_avg
          from asterisk_outbound_call_grade aocg
            join asterisk_outbound_call aoc on aocg.asterisk_outbound_call_id = aoc.id
          where true
            and aocg.deleted_at is null
            and aocg.created_at >= current_date::timestamp at time zone 'us/central' - interval '7 days'
            and exists(select 1 from sitel_users su where aoc.user_id = su.user_id)
          group by 1,2

Answer 1

我认为重写此SQL不需要检查/触摸数据，因为不兼容的SQL与数据和TZ转换有关。不管怎么说，这对你来说可能是个好开始：

WITH sitel_users as (
          select
            user_id
          from validation_rep vr
          where true
           and vr.feature_flags:VARCHAR = 'is_bogota'
           /* and (vr.feature_flags ->> 'is_bogota')::bool is true */
        ), call_grade as (
          select
              aoc.user_id
            , convert_timezone( 'America/Chicago', convert_timezone( 'UTC', d )  )::DATE  as  call_grade_date
             /* (aocg.created_at at time zone 'z' at time zone 'us/central' )::date   as  call_grade_date */
            , avg(aocg.score) call_grade_avg
          from asterisk_outbound_call_grade aocg
            join asterisk_outbound_call aoc on aocg.asterisk_outbound_call_id = aoc.id
          where true
            and aocg.deleted_at is null
            and aocg.created_at >= convert_timezone('America/Chicago', current_date ) - interval '7 days' 
            /*  and aocg.created_at >= current_date::timestamp at time zone 'us/central' - interval '7 days' */
            and exists(select 1 from sitel_users su where aoc.user_id = su.user_id)
          group by 1,2