Skip to main content

Continuity Problem Best Practices

Requirement Scenarios

The so-called continuity problems include:

  1. Complete size (color) rate (footwear and apparel): In a certain product's colors, if more than 4 consecutive sizes have inventory, it is considered complete size

  2. Consecutive ordering (purchasing) months (member-related)······

In daily report creation, continuity-related problems are often encountered. The following solutions are provided for reference:

Solution Approach

  1. Arrange the data in a certain order (such as: by size, color, time, etc.)

  2. Label the content to be determined (such as: whether there is inventory, yes is 1, no is 0)

  3. Use row_number() to record the position of each value;

  4. Group by label, use collect_list() function to record the row_number() information of 0 or 1;

  5. Select the array with label 1, use the characteristic of consecutive values (max-min+1=number of values in the array) to calculate related content

Case Implementation

The following example uses the complete size rate of Red color for a certain product.

  1. Arrange the relevant inventory data by size;

image.png

  1. Label by whether there is inventory, with inventory as: 1, without inventory as: 0;

    image.png

  2. Use row_number() to record the row numbers of all values;

image.png

  1. Group by whether there is inventory label, use collect_list() function to record the position information of 0 or 1;

image.png

  1. Select the array with label 1, use the characteristic of consecutive values (max-min+1=number of values in the array) to calculate related content, calculate the consecutive number of sizes with inventory;

  2. Final effect: In the array with label "1", maximum value: 5, minimum value: 2, number of values in the array: 4. Then:
    Maximum value - Minimum value + 1 = Number of values in the array; i.e.: 5-2+1=4. Therefore, this product's Red color sizes are complete.