Continuity Issue Best Practices

Requirement Scenario

Continuity issues include:

1. Complete size and color rate, used in footwear and apparel: for a product color, if more than four consecutive sizes have inventory, the size set is considered complete.

2. Number of consecutive ordering or purchase months, often used in member-related analysis.

Continuity issues are often encountered when creating daily reports. The following solution is provided for reference.

Solution

1. Sort the data in a certain order, such as by size, color, or time.
2. Label the content to be judged, such as whether inventory exists: yes is 1, no is 0.
3. Use row_number() to record the position of each value.
4. Group by label and use the collect_list() function to record the row_number() information for 0 or 1.
5. Select the array where the label is 1, then use the property of consecutive values, namely max - min + 1 = number of values in the array, to calculate the related content.

Case Implementation

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

1. Sort the related inventory data by size.

1. Add labels based on whether inventory exists. Inventory exists: 1. No inventory: 0.

   

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

1. Group by the inventory label and use the collect_list() function to record position information for 0 or 1.

1. Select the array where the label is 1, then use the property of consecutive values, max - min + 1 = number of values in the array, to calculate the related content, namely the number of consecutive sizes with inventory.
2. Final result: in the array with label "1", the maximum value is 5, the minimum value is 2, and the number of values in the array is 4. Therefore:
   maximum - minimum + 1 = number of values in the array, namely 5 - 2 + 1 = 4. Therefore, for this product, the RED color has a complete size set.