Skip to main content

Year-over-Year and Month-over-Month End-of-Month Comparison Issues

[Before Optimization]

Month-over-year comparison may show one-to-many situations, such as March 30th and March 31st both corresponding to April 30th, resulting in two data entries for April 30th.

image.png

[After Optimization]

According to the rule of "current date -1 months" to find the comparison date.

Note: According to the above rule, Spark defaults to end-of-month alignment, MySQL, PG, etc. are date-aligned, and other databases have their own rules.

Guanindex datasets, file datasets, etc. are processed according to Spark rules, while direct connection datasets are processed according to their respective database rules.

image.png

Summary

The optimized logic is:

Spark defaults to end-of-month alignment (May 31st and May 30th both compare to April 30th data, April 30th compares to March 30th data), guanindex datasets, file datasets, ETL output datasets, etc. are processed according to Spark rules;

Direct connection datasets are processed according to their respective database rules, such as MySQL and ClickHouse being date-aligned (May 31st and May 30th both compare to April 30th data, April 30th compares to March 30th data)