A window-based TopN function must follow the modification requirements of a window table-valued function (TVF) and a TopN function. Therefore, a window-based TopN function supports fewer compatibility modifications. This topic describes how the compatibility between a job and the state data is affected after you modify a window-based TopN function in an SQL statement for the job.

Modifications that do not affect or partially affect the compatibility

  • Add or delete a window attribute field. After this modification, the job remains fully compatible with the state data. 
    -- Original SQL statement: 
select a, b, c, window_start from (
  select *,
      row_number() over (partition by b, window_start, window_end order by c) as rk
  from (
    select a,
      sum(b) as b,
      max(c) as c,
        window_start,
        window_end
    from table (tumble(table MyTable, descriptor(ts), interval '1' minute))
      group by a, window_start, window_end)
  ) where rk < 3;


-- Add the window end attribute. After this modification, the job remains fully compatible with the state data. 
select a, b, c, window_start, window_end from (
  select *,
      row_number() over (partition by b, window_start, window_end order by c) as rk
  from (
    select a,
      sum(b) as b,
      max(c) as c,
        window_start,
        window_end
    from table (tumble(table MyTable, descriptor(ts), interval '1' minute))
      group by a, window_start, window_end)
  ) where rk < 3;
  • The compatibility between a job and the state data is not affected regardless of whether the rk field is specified in a statement to generate the rank number. 
    -- Original SQL statement: 
select a, b, c, window_start from (
  select *,
      row_number() over (partition by b, window_start, window_end order by c) as rk
  from (
    select a,
      sum(b) as b,
      max(c) as c,
        window_start,
        window_end
    from table (tumble(table MyTable, descriptor(ts), interval '1' minute))
      group by a, window_start, window_end)
  ) where rk < 3;


-- Add the rk field to the statement to generate the rank number. After this modification, the job remains fully compatible with the state data. 
select a, b, c, window_start, rk from (
  select *,
      row_number() over (partition by b, window_start, window_end order by c) as rk
  from (
    select a,
      sum(b) as b,
      max(c) as c,
        window_start,
        window_end
    from table (tumble(table MyTable, descriptor(ts), interval '1' minute))
      group by a, window_start, window_end)
  ) where rk < 3;

Modifications that cause full incompatibility

  • If you modify window-related attributes, such as the window type, window size, and time-related attributes, the job becomes incompatible with the state data.

    For more information about the example of modifying window-related attributes, see Modifications that cause full incompatibility provided in "Window TVF."

  • If you add, delete, or modify a statistical dimension or change the computing logic of a field that is related to the statistical dimension in the SQL statement for a job, the job becomes incompatible with the state data. Statistical dimensions are specified by keys in GROUP BY.

    For more information about the example of changing a key in GROUP BY, see Modifications that cause full incompatibility provided in "Window TVF."

  • If you add, delete, modify a statistical metric, the job becomes incompatible with the state data. This modification leads to a change in the input of a TopN function. As a result, the job becomes incompatible with the state data. 
    -- Original SQL statement: 
select a, b, c, window_start from (
  select *,
      row_number() over (partition by b, window_start, window_end order by c) as rk
  from (
    select a,
      sum(b) as b,
      max(c) as c,
        window_start,
        window_end
    from table (tumble(table MyTable, descriptor(ts), interval '1' minute))
      group by a, window_start, window_end)
  ) where rk < 3;

-- Add a statistical metric min(d) as d. After this modification, the job becomes incompatible with the state data. 
-- This modification leads to a change in the input of a TopN function. 
select a, b, c, d, window_start from (
  select *,
      row_number() over (partition by b, window_start, window_end order by c) as rk
  from (
    select a,
      sum(b) as b,
      max(c) as c,
      min(d) as d,
        window_start,
        window_end
    from table (tumble(table MyTable, descriptor(ts), interval '1' minute))
      group by a, window_start, window_end)
  ) where rk < 3;
  • If you add, delete, or modify a key in PARTITION BY or change the computing logic of a key in PARTITION BY, the job becomes incompatible with the state data.

    For more information about the example of changing a key in PARTITION BY, see Modifications that cause full incompatibility provided in "TopN function."

  • If you modify an attribute related to ORDER BY, such as the sorting fields and direction, the job becomes incompatible with the state data.

    For more information about the example of changing a key in ORDER BY, see Modifications that cause full incompatibility provided in "TopN function."

  • If you change the value of N in the TopN function, the job becomes incompatible with the state data.

    For more information about the example of changing the value of N in a TopN function, see Modifications that cause full incompatibility provided in "TopN function."