StarRocks query performance with Apache Iceberg

[alberttwong]

Using TPC-DS data of 1GB with Apache Iceberg tables in Tabular. Ran TPC-DS query 57. Use this article to create the test environment https://atwong.medium.com/easiest-way-to-load-tpc-ds-data-into-postgresql-1ebd83871a07.

StarRocks > set new_planner_optimize_timeout=10000;
Query OK, 0 rows affected (0.03 sec)

StarRocks > with v1 as(
    ->   select i_category, i_brand, cc_name, d_year, d_moy,
    ->         sum(cs_sales_price) sum_sales,
    ->         avg(sum(cs_sales_price)) over
    ->           (partition by i_category, i_brand,
    ->                      cc_name, d_year)
    ->           avg_monthly_sales,
    ->         rank() over
    ->           (partition by i_category, i_brand,
    ->                      cc_name
    ->            order by d_year, d_moy) rn
    ->   from item, catalog_sales, date_dim, call_center
    ->   where cs_item_sk = i_item_sk and
    ->        cs_sold_date_sk = d_date_sk and
    ->        cc_call_center_sk= cs_call_center_sk and
    ->        (
    ->          d_year = 1999 or
    ->          ( d_year = 1999-1 and d_moy =12) or
    ->          ( d_year = 1999+1 and d_moy =1)
    ->        )
    ->   group by i_category, i_brand,
    ->           cc_name , d_year, d_moy),
    -> v2 as(
    ->   select v1.i_category ,v1.d_year, v1.d_moy ,v1.avg_monthly_sales
    ->         ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
    ->   from v1, v1 v1_lag, v1 v1_lead
    ->   where v1.i_category = v1_lag.i_category and
    ->        v1.i_category = v1_lead.i_category and
    ->        v1.i_brand = v1_lag.i_brand and
    ->        v1.i_brand = v1_lead.i_brand and
    ->        v1.cc_name = v1_lag.cc_name and
    ->        v1.cc_name = v1_lead.cc_name and
    ->        v1.rn = v1_lag.rn + 1 and
    ->        v1.rn = v1_lead.rn - 1)
    -> select  *
    -> from v2
    -> where  d_year = 1999 and
    ->         avg_monthly_sales > 0 and
    ->         case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
    -> order by sum_sales - avg_monthly_sales, 3
    -> limit 100;
+------------+--------+-------+-------------------+-----------+----------+----------+
| i_category | d_year | d_moy | avg_monthly_sales | sum_sales | psum     | nsum     |
+------------+--------+-------+-------------------+-----------+----------+----------+
| Men        |   1999 |     1 |     7536.16750000 |   2822.22 | 15366.77 |  4568.91 |
| Music      |   1999 |     2 |     7305.85833333 |   2838.84 |  3746.83 |  4682.73 |
| Shoes      |   1999 |     7 |     6915.22083333 |   2512.46 |  4768.59 |  8884.76 |
| Men        |   1999 |     7 |     7482.29250000 |   3324.60 |  3422.34 | 11231.75 |
| Men        |   1999 |     7 |     6910.99083333 |   2759.95 |  4606.60 |  8151.58 |
| Music      |   1999 |     1 |     7960.88666667 |   3817.58 | 14931.20 |  4740.46 |
| Music      |   1999 |     5 |     7857.55250000 |   3718.35 |  4777.33 |  4717.90 |
| Children   |   1999 |     2 |     7660.09666667 |   3548.75 |  3736.29 |  5145.36 |
| Men        |   1999 |     6 |     7482.29250000 |   3422.34 |  4822.97 |  3324.60 |
| Music      |   1999 |     2 |     7665.45666667 |   3611.36 |  6319.82 |  4103.03 |
| Men        |   1999 |     2 |     7322.90916667 |   3300.35 |  4358.89 |  4926.22 |
| Children   |   1999 |     4 |     6550.79666667 |   2541.51 |  3559.00 |  3218.37 |
| Children   |   1999 |     3 |     7658.07166667 |   3665.09 |  4222.77 |  4205.54 |
| Music      |   1999 |     7 |     7960.88666667 |   3989.75 |  4391.21 | 11045.38 |
| Shoes      |   1999 |     5 |     6563.09833333 |   2623.85 |  3831.07 |  4494.57 |
| Children   |   1999 |     4 |     6834.03666667 |   2896.51 |  5840.07 |  3216.33 |
| Children   |   1999 |     1 |     7660.09666667 |   3736.29 | 14178.72 |  3548.75 |
| Women      |   1999 |     2 |     7017.65083333 |   3098.88 |  3740.95 |  4299.15 |
| Men        |   1999 |     5 |     7493.48250000 |   3595.76 |  4971.00 |  3726.76 |
| Men        |   1999 |     1 |     6797.43583333 |   2917.39 | 12037.30 |  4138.65 |
| Shoes      |   1999 |     1 |     7379.81500000 |   3509.89 | 14291.37 |  4788.05 |
| Music      |   1999 |     4 |     7647.74000000 |   3781.87 |  3913.83 |  4904.37 |
| Shoes      |   1999 |     4 |     7379.81500000 |   3514.14 |  3657.49 |  4624.27 |
| Shoes      |   1999 |     6 |     7090.45500000 |   3233.38 |  3598.32 |  6011.69 |
| Shoes      |   1999 |     2 |     6930.18416667 |   3091.93 |  3755.38 |  3333.08 |
| Shoes      |   1999 |     3 |     6846.16000000 |   3019.19 |  4003.60 |  3592.21 |
| Children   |   1999 |     3 |     7020.35000000 |   3201.74 |  4938.19 |  3874.13 |
| Children   |   1999 |     5 |     7660.09666667 |   3851.82 |  5433.95 |  5087.60 |
| Women      |   1999 |     5 |     7017.65083333 |   3212.82 |  5203.35 |  4474.02 |
| Music      |   1999 |     5 |     7960.88666667 |   4179.19 |  4726.96 |  4391.21 |
| Shoes      |   1999 |     2 |     6563.09833333 |   2791.95 |  4232.09 |  3822.87 |
| Men        |   1999 |     7 |     7536.16750000 |   3766.61 |  4447.43 | 10746.78 |
| Men        |   1999 |     6 |     7493.48250000 |   3726.76 |  3595.76 |  4645.91 |
| Children   |   1999 |     7 |     6501.39916667 |   2743.73 |  3732.23 |  8546.30 |
| Music      |   1999 |     3 |     7647.74000000 |   3913.83 |  5398.23 |  3781.87 |
| Children   |   1999 |     2 |     6597.22500000 |   2872.68 |  3245.57 |  3981.84 |
| Shoes      |   1999 |     2 |     6915.22083333 |   3192.51 |  3327.96 |  4322.20 |
| Shoes      |   1999 |     3 |     7379.81500000 |   3657.49 |  4788.05 |  3514.14 |
| Women      |   1999 |     2 |     6575.87583333 |   2871.61 |  4816.30 |  4012.52 |
| Shoes      |   1999 |     7 |     6563.09833333 |   2860.88 |  4494.57 |  9707.17 |
| Music      |   1999 |     2 |     6544.27083333 |   2844.89 |  2880.07 |  4428.12 |
| Children   |   1999 |     5 |     7658.07166667 |   3960.48 |  4205.54 |  5555.67 |
| Children   |   1999 |     2 |     6349.13416667 |   2657.28 |  3424.54 |  3589.34 |
| Music      |   1999 |     1 |     7857.55250000 |   4170.07 | 17003.92 |  4822.07 |
| Children   |   1999 |     3 |     6636.59166667 |   2954.35 |  3456.31 |  4807.28 |
| Men        |   1999 |     7 |     7469.45500000 |   3794.75 |  4925.20 |  7872.42 |
| Music      |   1999 |     4 |     6544.27083333 |   2872.12 |  4428.12 |  3693.28 |
| Men        |   1999 |     4 |     6910.99083333 |   3243.06 |  4144.89 |  5533.21 |
| Music      |   1999 |     1 |     6544.27083333 |   2880.07 | 14811.55 |  2844.89 |
| Women      |   1999 |     2 |     6916.93833333 |   3268.54 |  4739.63 |  3897.85 |
| Music      |   1999 |     3 |     7960.88666667 |   4316.31 |  4740.46 |  4726.96 |
| Men        |   1999 |     1 |     6634.77666667 |   2991.55 | 11153.02 |  3585.32 |
| Men        |   1999 |     7 |     7322.90916667 |   3698.98 |  4327.46 |  8961.51 |
| Children   |   1999 |     5 |     6834.03666667 |   3216.33 |  2896.51 |  3438.01 |
| Music      |   1999 |     4 |     6743.22500000 |   3135.57 |  3994.00 |  3689.15 |
| Music      |   1999 |     1 |     7014.37916667 |   3408.12 | 14101.77 |  3877.52 |
| Shoes      |   1999 |     3 |     6930.18416667 |   3333.08 |  3091.93 |  5277.39 |
| Children   |   1999 |     5 |     6244.49666667 |   2648.52 |  3340.04 |  3061.51 |
| Shoes      |   1999 |     4 |     7151.23083333 |   3559.33 |  4220.40 |  3908.65 |
| Shoes      |   1999 |     1 |     6915.22083333 |   3327.96 | 15438.89 |  3192.51 |
| Women      |   1999 |     7 |     6575.87583333 |   2996.45 |  3738.13 |  8393.38 |
| Music      |   1999 |     6 |     7960.88666667 |   4391.21 |  4179.19 |  3989.75 |
| Men        |   1999 |     1 |     7482.29250000 |   3913.10 | 17580.61 |  4660.72 |
| Music      |   1999 |     3 |     7665.45666667 |   4103.03 |  3611.36 |  4931.39 |
| Music      |   1999 |     1 |     7305.85833333 |   3746.83 | 13654.32 |  2838.84 |
| Women      |   1999 |     1 |     6473.30083333 |   2918.56 | 12318.30 |  3441.14 |
| Children   |   1999 |     3 |     6244.49666667 |   2692.40 |  3417.66 |  3340.04 |
| Shoes      |   1999 |     5 |     6915.22083333 |   3363.53 |  4413.60 |  4768.59 |
| Music      |   1999 |     3 |     7857.55250000 |   4320.92 |  4822.07 |  4777.33 |
| Music      |   1999 |     4 |     7305.85833333 |   3787.89 |  4682.73 |  4897.65 |
| Shoes      |   1999 |     2 |     6564.11833333 |   3046.29 |  4298.34 |  4688.65 |
| Shoes      |   1999 |     5 |     7090.45500000 |   3598.32 |  3885.42 |  3233.38 |
| Women      |   1999 |     7 |     6415.25500000 |   2931.40 |  3859.54 |  8772.65 |
| Men        |   1999 |     3 |     7469.45500000 |   3997.96 |  4454.62 |  4856.96 |
| Men        |   1999 |     4 |     7536.16750000 |   4070.53 |  4616.46 |  5193.62 |
| Children   |   1999 |     4 |     7658.07166667 |   4205.54 |  3665.09 |  3960.48 |
| Women      |   1999 |     6 |     6735.32500000 |   3289.16 |  3810.92 |  3421.56 |
| Women      |   1999 |     4 |     6473.30083333 |   3029.66 |  4066.84 |  4452.18 |
| Women      |   1999 |     4 |     6030.88583333 |   2590.14 |  4099.64 |  3348.41 |
| Children   |   1999 |     2 |     7658.07166667 |   4222.77 |  4822.93 |  3665.09 |
| Music      |   1999 |     6 |     7106.58916667 |   3672.15 |  4348.83 |  4135.57 |
| Shoes      |   1999 |     6 |     6846.16000000 |   3427.30 |  4007.11 |  4791.14 |
| Shoes      |   1999 |     7 |     6930.18416667 |   3513.24 |  4324.92 |  8995.99 |
| Women      |   1999 |     5 |     6575.87583333 |   3159.42 |  3352.17 |  3738.13 |
| Children   |   1999 |     3 |     6501.39916667 |   3088.82 |  4844.96 |  3211.37 |
| Shoes      |   1999 |     7 |     7151.23083333 |   3739.28 |  4889.14 | 10018.74 |
| Children   |   1999 |     6 |     6834.03666667 |   3438.01 |  3216.33 |  3933.33 |
| Men        |   1999 |     4 |     6092.52750000 |   2705.47 |  3580.93 |  3946.78 |
| Men        |   1999 |     1 |     7469.45500000 |   4084.37 | 15702.94 |  4454.62 |
| Shoes      |   1999 |     5 |     6930.18416667 |   3547.09 |  5277.39 |  4324.92 |
| Music      |   1999 |     7 |     6544.27083333 |   3169.16 |  3599.01 |  9674.19 |
| Music      |   1999 |     6 |     7665.45666667 |   4303.32 |  4646.23 |  6216.04 |
| Shoes      |   1999 |     2 |     7151.23083333 |   3794.46 |  4267.59 |  4220.40 |
| Music      |   1999 |     6 |     7305.85833333 |   3950.95 |  4897.65 |  4674.19 |
| Children   |   1999 |     1 |     6597.22500000 |   3245.57 | 13023.89 |  2872.68 |
| Music      |   1999 |     3 |     7014.37916667 |   3662.76 |  3877.52 |  4038.49 |
| Music      |   1999 |     2 |     5840.58750000 |   2490.51 |  3128.09 |  3883.31 |
| Men        |   1999 |     2 |     6092.52750000 |   2749.45 |  3614.00 |  3580.93 |
| Shoes      |   1999 |     6 |     7379.81500000 |   4039.47 |  4624.27 |  4832.28 |
| Children   |   1999 |     5 |     6550.79666667 |   3218.37 |  2541.51 |  4617.26 |
+------------+--------+-------+-------------------+-----------+----------+----------+
100 rows in set (13.04 sec)

StarRocks > with v1 as(
    ->   select i_category, i_brand, cc_name, d_year, d_moy,
    ->         sum(cs_sales_price) sum_sales,
    ->         avg(sum(cs_sales_price)) over
    ->           (partition by i_category, i_brand,
    ->                      cc_name, d_year)
    ->           avg_monthly_sales,
    ->         rank() over
    ->           (partition by i_category, i_brand,
    ->                      cc_name
    ->            order by d_year, d_moy) rn
    ->   from item, catalog_sales, date_dim, call_center
    ->   where cs_item_sk = i_item_sk and
    ->        cs_sold_date_sk = d_date_sk and
    ->        cc_call_center_sk= cs_call_center_sk and
    ->        (
    ->          d_year = 1999 or
    ->          ( d_year = 1999-1 and d_moy =12) or
    ->          ( d_year = 1999+1 and d_moy =1)
    ->        )
    ->   group by i_category, i_brand,
    ->           cc_name , d_year, d_moy),
    -> v2 as(
    ->   select v1.i_category ,v1.d_year, v1.d_moy ,v1.avg_monthly_sales
    ->         ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
    ->   from v1, v1 v1_lag, v1 v1_lead
    ->   where v1.i_category = v1_lag.i_category and
    ->        v1.i_category = v1_lead.i_category and
    ->        v1.i_brand = v1_lag.i_brand and
    ->        v1.i_brand = v1_lead.i_brand and
    ->        v1.cc_name = v1_lag.cc_name and
    ->        v1.cc_name = v1_lead.cc_name and
    ->        v1.rn = v1_lag.rn + 1 and
    ->        v1.rn = v1_lead.rn - 1)
    -> select  *
    -> from v2
    -> where  d_year = 1999 and
    ->         avg_monthly_sales > 0 and
    ->         case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
    -> order by sum_sales - avg_monthly_sales, 3
    -> limit 100;
+------------+--------+-------+-------------------+-----------+----------+----------+
| i_category | d_year | d_moy | avg_monthly_sales | sum_sales | psum     | nsum     |
+------------+--------+-------+-------------------+-----------+----------+----------+
| Men        |   1999 |     1 |     7536.16750000 |   2822.22 | 15366.77 |  4568.91 |
| Music      |   1999 |     2 |     7305.85833333 |   2838.84 |  3746.83 |  4682.73 |
| Shoes      |   1999 |     7 |     6915.22083333 |   2512.46 |  4768.59 |  8884.76 |
| Men        |   1999 |     7 |     7482.29250000 |   3324.60 |  3422.34 | 11231.75 |
| Men        |   1999 |     7 |     6910.99083333 |   2759.95 |  4606.60 |  8151.58 |
| Music      |   1999 |     1 |     7960.88666667 |   3817.58 | 14931.20 |  4740.46 |
| Music      |   1999 |     5 |     7857.55250000 |   3718.35 |  4777.33 |  4717.90 |
| Children   |   1999 |     2 |     7660.09666667 |   3548.75 |  3736.29 |  5145.36 |
| Men        |   1999 |     6 |     7482.29250000 |   3422.34 |  4822.97 |  3324.60 |
| Music      |   1999 |     2 |     7665.45666667 |   3611.36 |  6319.82 |  4103.03 |
| Men        |   1999 |     2 |     7322.90916667 |   3300.35 |  4358.89 |  4926.22 |
| Children   |   1999 |     4 |     6550.79666667 |   2541.51 |  3559.00 |  3218.37 |
| Children   |   1999 |     3 |     7658.07166667 |   3665.09 |  4222.77 |  4205.54 |
| Music      |   1999 |     7 |     7960.88666667 |   3989.75 |  4391.21 | 11045.38 |
| Shoes      |   1999 |     5 |     6563.09833333 |   2623.85 |  3831.07 |  4494.57 |
| Children   |   1999 |     4 |     6834.03666667 |   2896.51 |  5840.07 |  3216.33 |
| Children   |   1999 |     1 |     7660.09666667 |   3736.29 | 14178.72 |  3548.75 |
| Women      |   1999 |     2 |     7017.65083333 |   3098.88 |  3740.95 |  4299.15 |
| Men        |   1999 |     5 |     7493.48250000 |   3595.76 |  4971.00 |  3726.76 |
| Men        |   1999 |     1 |     6797.43583333 |   2917.39 | 12037.30 |  4138.65 |
| Shoes      |   1999 |     1 |     7379.81500000 |   3509.89 | 14291.37 |  4788.05 |
| Music      |   1999 |     4 |     7647.74000000 |   3781.87 |  3913.83 |  4904.37 |
| Shoes      |   1999 |     4 |     7379.81500000 |   3514.14 |  3657.49 |  4624.27 |
| Shoes      |   1999 |     6 |     7090.45500000 |   3233.38 |  3598.32 |  6011.69 |
| Shoes      |   1999 |     2 |     6930.18416667 |   3091.93 |  3755.38 |  3333.08 |
| Shoes      |   1999 |     3 |     6846.16000000 |   3019.19 |  4003.60 |  3592.21 |
| Children   |   1999 |     3 |     7020.35000000 |   3201.74 |  4938.19 |  3874.13 |
| Children   |   1999 |     5 |     7660.09666667 |   3851.82 |  5433.95 |  5087.60 |
| Women      |   1999 |     5 |     7017.65083333 |   3212.82 |  5203.35 |  4474.02 |
| Music      |   1999 |     5 |     7960.88666667 |   4179.19 |  4726.96 |  4391.21 |
| Shoes      |   1999 |     2 |     6563.09833333 |   2791.95 |  4232.09 |  3822.87 |
| Men        |   1999 |     7 |     7536.16750000 |   3766.61 |  4447.43 | 10746.78 |
| Men        |   1999 |     6 |     7493.48250000 |   3726.76 |  3595.76 |  4645.91 |
| Children   |   1999 |     7 |     6501.39916667 |   2743.73 |  3732.23 |  8546.30 |
| Music      |   1999 |     3 |     7647.74000000 |   3913.83 |  5398.23 |  3781.87 |
| Children   |   1999 |     2 |     6597.22500000 |   2872.68 |  3245.57 |  3981.84 |
| Shoes      |   1999 |     2 |     6915.22083333 |   3192.51 |  3327.96 |  4322.20 |
| Shoes      |   1999 |     3 |     7379.81500000 |   3657.49 |  4788.05 |  3514.14 |
| Women      |   1999 |     2 |     6575.87583333 |   2871.61 |  4816.30 |  4012.52 |
| Shoes      |   1999 |     7 |     6563.09833333 |   2860.88 |  4494.57 |  9707.17 |
| Music      |   1999 |     2 |     6544.27083333 |   2844.89 |  2880.07 |  4428.12 |
| Children   |   1999 |     5 |     7658.07166667 |   3960.48 |  4205.54 |  5555.67 |
| Children   |   1999 |     2 |     6349.13416667 |   2657.28 |  3424.54 |  3589.34 |
| Music      |   1999 |     1 |     7857.55250000 |   4170.07 | 17003.92 |  4822.07 |
| Children   |   1999 |     3 |     6636.59166667 |   2954.35 |  3456.31 |  4807.28 |
| Men        |   1999 |     7 |     7469.45500000 |   3794.75 |  4925.20 |  7872.42 |
| Music      |   1999 |     4 |     6544.27083333 |   2872.12 |  4428.12 |  3693.28 |
| Men        |   1999 |     4 |     6910.99083333 |   3243.06 |  4144.89 |  5533.21 |
| Music      |   1999 |     1 |     6544.27083333 |   2880.07 | 14811.55 |  2844.89 |
| Women      |   1999 |     2 |     6916.93833333 |   3268.54 |  4739.63 |  3897.85 |
| Music      |   1999 |     3 |     7960.88666667 |   4316.31 |  4740.46 |  4726.96 |
| Men        |   1999 |     1 |     6634.77666667 |   2991.55 | 11153.02 |  3585.32 |
| Men        |   1999 |     7 |     7322.90916667 |   3698.98 |  4327.46 |  8961.51 |
| Children   |   1999 |     5 |     6834.03666667 |   3216.33 |  2896.51 |  3438.01 |
| Music      |   1999 |     4 |     6743.22500000 |   3135.57 |  3994.00 |  3689.15 |
| Music      |   1999 |     1 |     7014.37916667 |   3408.12 | 14101.77 |  3877.52 |
| Shoes      |   1999 |     3 |     6930.18416667 |   3333.08 |  3091.93 |  5277.39 |
| Children   |   1999 |     5 |     6244.49666667 |   2648.52 |  3340.04 |  3061.51 |
| Shoes      |   1999 |     4 |     7151.23083333 |   3559.33 |  4220.40 |  3908.65 |
| Shoes      |   1999 |     1 |     6915.22083333 |   3327.96 | 15438.89 |  3192.51 |
| Women      |   1999 |     7 |     6575.87583333 |   2996.45 |  3738.13 |  8393.38 |
| Music      |   1999 |     6 |     7960.88666667 |   4391.21 |  4179.19 |  3989.75 |
| Men        |   1999 |     1 |     7482.29250000 |   3913.10 | 17580.61 |  4660.72 |
| Music      |   1999 |     3 |     7665.45666667 |   4103.03 |  3611.36 |  4931.39 |
| Music      |   1999 |     1 |     7305.85833333 |   3746.83 | 13654.32 |  2838.84 |
| Women      |   1999 |     1 |     6473.30083333 |   2918.56 | 12318.30 |  3441.14 |
| Children   |   1999 |     3 |     6244.49666667 |   2692.40 |  3417.66 |  3340.04 |
| Shoes      |   1999 |     5 |     6915.22083333 |   3363.53 |  4413.60 |  4768.59 |
| Music      |   1999 |     3 |     7857.55250000 |   4320.92 |  4822.07 |  4777.33 |
| Music      |   1999 |     4 |     7305.85833333 |   3787.89 |  4682.73 |  4897.65 |
| Shoes      |   1999 |     2 |     6564.11833333 |   3046.29 |  4298.34 |  4688.65 |
| Shoes      |   1999 |     5 |     7090.45500000 |   3598.32 |  3885.42 |  3233.38 |
| Women      |   1999 |     7 |     6415.25500000 |   2931.40 |  3859.54 |  8772.65 |
| Men        |   1999 |     3 |     7469.45500000 |   3997.96 |  4454.62 |  4856.96 |
| Men        |   1999 |     4 |     7536.16750000 |   4070.53 |  4616.46 |  5193.62 |
| Children   |   1999 |     4 |     7658.07166667 |   4205.54 |  3665.09 |  3960.48 |
| Women      |   1999 |     6 |     6735.32500000 |   3289.16 |  3810.92 |  3421.56 |
| Women      |   1999 |     4 |     6473.30083333 |   3029.66 |  4066.84 |  4452.18 |
| Women      |   1999 |     4 |     6030.88583333 |   2590.14 |  4099.64 |  3348.41 |
| Children   |   1999 |     2 |     7658.07166667 |   4222.77 |  4822.93 |  3665.09 |
| Music      |   1999 |     6 |     7106.58916667 |   3672.15 |  4348.83 |  4135.57 |
| Shoes      |   1999 |     6 |     6846.16000000 |   3427.30 |  4007.11 |  4791.14 |
| Shoes      |   1999 |     7 |     6930.18416667 |   3513.24 |  4324.92 |  8995.99 |
| Women      |   1999 |     5 |     6575.87583333 |   3159.42 |  3352.17 |  3738.13 |
| Children   |   1999 |     3 |     6501.39916667 |   3088.82 |  4844.96 |  3211.37 |
| Shoes      |   1999 |     7 |     7151.23083333 |   3739.28 |  4889.14 | 10018.74 |
| Children   |   1999 |     6 |     6834.03666667 |   3438.01 |  3216.33 |  3933.33 |
| Men        |   1999 |     4 |     6092.52750000 |   2705.47 |  3580.93 |  3946.78 |
| Men        |   1999 |     1 |     7469.45500000 |   4084.37 | 15702.94 |  4454.62 |
| Shoes      |   1999 |     5 |     6930.18416667 |   3547.09 |  5277.39 |  4324.92 |
| Music      |   1999 |     7 |     6544.27083333 |   3169.16 |  3599.01 |  9674.19 |
| Music      |   1999 |     6 |     7665.45666667 |   4303.32 |  4646.23 |  6216.04 |
| Shoes      |   1999 |     2 |     7151.23083333 |   3794.46 |  4267.59 |  4220.40 |
| Music      |   1999 |     6 |     7305.85833333 |   3950.95 |  4897.65 |  4674.19 |
| Children   |   1999 |     1 |     6597.22500000 |   3245.57 | 13023.89 |  2872.68 |
| Music      |   1999 |     3 |     7014.37916667 |   3662.76 |  3877.52 |  4038.49 |
| Music      |   1999 |     2 |     5840.58750000 |   2490.51 |  3128.09 |  3883.31 |
| Men        |   1999 |     2 |     6092.52750000 |   2749.45 |  3614.00 |  3580.93 |
| Shoes      |   1999 |     6 |     7379.81500000 |   4039.47 |  4624.27 |  4832.28 |
| Children   |   1999 |     5 |     6550.79666667 |   3218.37 |  2541.51 |  4617.26 |
+------------+--------+-------+-------------------+-----------+----------+----------+
100 rows in set (6.49 sec)

StarRocks > with v1 as(
    ->   select i_category, i_brand, cc_name, d_year, d_moy,
    ->         sum(cs_sales_price) sum_sales,
    ->         avg(sum(cs_sales_price)) over
    ->           (partition by i_category, i_brand,
    ->                      cc_name, d_year)
    ->           avg_monthly_sales,
    ->         rank() over
    ->           (partition by i_category, i_brand,
    ->                      cc_name
    ->            order by d_year, d_moy) rn
    ->   from item, catalog_sales, date_dim, call_center
    ->   where cs_item_sk = i_item_sk and
    ->        cs_sold_date_sk = d_date_sk and
    ->        cc_call_center_sk= cs_call_center_sk and
    ->        (
    ->          d_year = 1999 or
    ->          ( d_year = 1999-1 and d_moy =12) or
    ->          ( d_year = 1999+1 and d_moy =1)
    ->        )
    ->   group by i_category, i_brand,
    ->           cc_name , d_year, d_moy),
    -> v2 as(
    ->   select v1.i_category ,v1.d_year, v1.d_moy ,v1.avg_monthly_sales
    ->         ,v1.sum_sales, v1_lag.sum_sales psum, v1_lead.sum_sales nsum
    ->   from v1, v1 v1_lag, v1 v1_lead
    ->   where v1.i_category = v1_lag.i_category and
    ->        v1.i_category = v1_lead.i_category and
    ->        v1.i_brand = v1_lag.i_brand and
    ->        v1.i_brand = v1_lead.i_brand and
    ->        v1.cc_name = v1_lag.cc_name and
    ->        v1.cc_name = v1_lead.cc_name and
    ->        v1.rn = v1_lag.rn + 1 and
    ->        v1.rn = v1_lead.rn - 1)
    -> select  *
    -> from v2
    -> where  d_year = 1999 and
    ->         avg_monthly_sales > 0 and
    ->         case when avg_monthly_sales > 0 then abs(sum_sales - avg_monthly_sales) / avg_monthly_sales else null end > 0.1
    -> order by sum_sales - avg_monthly_sales, 3
    -> limit 100;
+------------+--------+-------+-------------------+-----------+----------+----------+
| i_category | d_year | d_moy | avg_monthly_sales | sum_sales | psum     | nsum     |
+------------+--------+-------+-------------------+-----------+----------+----------+
| Men        |   1999 |     1 |     7536.16750000 |   2822.22 | 15366.77 |  4568.91 |
| Music      |   1999 |     2 |     7305.85833333 |   2838.84 |  3746.83 |  4682.73 |
| Shoes      |   1999 |     7 |     6915.22083333 |   2512.46 |  4768.59 |  8884.76 |
| Men        |   1999 |     7 |     7482.29250000 |   3324.60 |  3422.34 | 11231.75 |
| Men        |   1999 |     7 |     6910.99083333 |   2759.95 |  4606.60 |  8151.58 |
| Music      |   1999 |     1 |     7960.88666667 |   3817.58 | 14931.20 |  4740.46 |
| Music      |   1999 |     5 |     7857.55250000 |   3718.35 |  4777.33 |  4717.90 |
| Children   |   1999 |     2 |     7660.09666667 |   3548.75 |  3736.29 |  5145.36 |
| Men        |   1999 |     6 |     7482.29250000 |   3422.34 |  4822.97 |  3324.60 |
| Music      |   1999 |     2 |     7665.45666667 |   3611.36 |  6319.82 |  4103.03 |
| Men        |   1999 |     2 |     7322.90916667 |   3300.35 |  4358.89 |  4926.22 |
| Children   |   1999 |     4 |     6550.79666667 |   2541.51 |  3559.00 |  3218.37 |
| Children   |   1999 |     3 |     7658.07166667 |   3665.09 |  4222.77 |  4205.54 |
| Music      |   1999 |     7 |     7960.88666667 |   3989.75 |  4391.21 | 11045.38 |
| Shoes      |   1999 |     5 |     6563.09833333 |   2623.85 |  3831.07 |  4494.57 |
| Children   |   1999 |     4 |     6834.03666667 |   2896.51 |  5840.07 |  3216.33 |
| Children   |   1999 |     1 |     7660.09666667 |   3736.29 | 14178.72 |  3548.75 |
| Women      |   1999 |     2 |     7017.65083333 |   3098.88 |  3740.95 |  4299.15 |
| Men        |   1999 |     5 |     7493.48250000 |   3595.76 |  4971.00 |  3726.76 |
| Men        |   1999 |     1 |     6797.43583333 |   2917.39 | 12037.30 |  4138.65 |
| Shoes      |   1999 |     1 |     7379.81500000 |   3509.89 | 14291.37 |  4788.05 |
| Music      |   1999 |     4 |     7647.74000000 |   3781.87 |  3913.83 |  4904.37 |
| Shoes      |   1999 |     4 |     7379.81500000 |   3514.14 |  3657.49 |  4624.27 |
| Shoes      |   1999 |     6 |     7090.45500000 |   3233.38 |  3598.32 |  6011.69 |
| Shoes      |   1999 |     2 |     6930.18416667 |   3091.93 |  3755.38 |  3333.08 |
| Shoes      |   1999 |     3 |     6846.16000000 |   3019.19 |  4003.60 |  3592.21 |
| Children   |   1999 |     3 |     7020.35000000 |   3201.74 |  4938.19 |  3874.13 |
| Children   |   1999 |     5 |     7660.09666667 |   3851.82 |  5433.95 |  5087.60 |
| Women      |   1999 |     5 |     7017.65083333 |   3212.82 |  5203.35 |  4474.02 |
| Music      |   1999 |     5 |     7960.88666667 |   4179.19 |  4726.96 |  4391.21 |
| Shoes      |   1999 |     2 |     6563.09833333 |   2791.95 |  4232.09 |  3822.87 |
| Men        |   1999 |     7 |     7536.16750000 |   3766.61 |  4447.43 | 10746.78 |
| Men        |   1999 |     6 |     7493.48250000 |   3726.76 |  3595.76 |  4645.91 |
| Children   |   1999 |     7 |     6501.39916667 |   2743.73 |  3732.23 |  8546.30 |
| Music      |   1999 |     3 |     7647.74000000 |   3913.83 |  5398.23 |  3781.87 |
| Children   |   1999 |     2 |     6597.22500000 |   2872.68 |  3245.57 |  3981.84 |
| Shoes      |   1999 |     2 |     6915.22083333 |   3192.51 |  3327.96 |  4322.20 |
| Shoes      |   1999 |     3 |     7379.81500000 |   3657.49 |  4788.05 |  3514.14 |
| Women      |   1999 |     2 |     6575.87583333 |   2871.61 |  4816.30 |  4012.52 |
| Shoes      |   1999 |     7 |     6563.09833333 |   2860.88 |  4494.57 |  9707.17 |
| Music      |   1999 |     2 |     6544.27083333 |   2844.89 |  2880.07 |  4428.12 |
| Children   |   1999 |     5 |     7658.07166667 |   3960.48 |  4205.54 |  5555.67 |
| Children   |   1999 |     2 |     6349.13416667 |   2657.28 |  3424.54 |  3589.34 |
| Music      |   1999 |     1 |     7857.55250000 |   4170.07 | 17003.92 |  4822.07 |
| Children   |   1999 |     3 |     6636.59166667 |   2954.35 |  3456.31 |  4807.28 |
| Men        |   1999 |     7 |     7469.45500000 |   3794.75 |  4925.20 |  7872.42 |
| Music      |   1999 |     4 |     6544.27083333 |   2872.12 |  4428.12 |  3693.28 |
| Men        |   1999 |     4 |     6910.99083333 |   3243.06 |  4144.89 |  5533.21 |
| Music      |   1999 |     1 |     6544.27083333 |   2880.07 | 14811.55 |  2844.89 |
| Women      |   1999 |     2 |     6916.93833333 |   3268.54 |  4739.63 |  3897.85 |
| Music      |   1999 |     3 |     7960.88666667 |   4316.31 |  4740.46 |  4726.96 |
| Men        |   1999 |     1 |     6634.77666667 |   2991.55 | 11153.02 |  3585.32 |
| Men        |   1999 |     7 |     7322.90916667 |   3698.98 |  4327.46 |  8961.51 |
| Children   |   1999 |     5 |     6834.03666667 |   3216.33 |  2896.51 |  3438.01 |
| Music      |   1999 |     4 |     6743.22500000 |   3135.57 |  3994.00 |  3689.15 |
| Music      |   1999 |     1 |     7014.37916667 |   3408.12 | 14101.77 |  3877.52 |
| Shoes      |   1999 |     3 |     6930.18416667 |   3333.08 |  3091.93 |  5277.39 |
| Children   |   1999 |     5 |     6244.49666667 |   2648.52 |  3340.04 |  3061.51 |
| Shoes      |   1999 |     4 |     7151.23083333 |   3559.33 |  4220.40 |  3908.65 |
| Shoes      |   1999 |     1 |     6915.22083333 |   3327.96 | 15438.89 |  3192.51 |
| Women      |   1999 |     7 |     6575.87583333 |   2996.45 |  3738.13 |  8393.38 |
| Music      |   1999 |     6 |     7960.88666667 |   4391.21 |  4179.19 |  3989.75 |
| Men        |   1999 |     1 |     7482.29250000 |   3913.10 | 17580.61 |  4660.72 |
| Music      |   1999 |     3 |     7665.45666667 |   4103.03 |  3611.36 |  4931.39 |
| Music      |   1999 |     1 |     7305.85833333 |   3746.83 | 13654.32 |  2838.84 |
| Women      |   1999 |     1 |     6473.30083333 |   2918.56 | 12318.30 |  3441.14 |
| Children   |   1999 |     3 |     6244.49666667 |   2692.40 |  3417.66 |  3340.04 |
| Shoes      |   1999 |     5 |     6915.22083333 |   3363.53 |  4413.60 |  4768.59 |
| Music      |   1999 |     3 |     7857.55250000 |   4320.92 |  4822.07 |  4777.33 |
| Music      |   1999 |     4 |     7305.85833333 |   3787.89 |  4682.73 |  4897.65 |
| Shoes      |   1999 |     2 |     6564.11833333 |   3046.29 |  4298.34 |  4688.65 |
| Shoes      |   1999 |     5 |     7090.45500000 |   3598.32 |  3885.42 |  3233.38 |
| Women      |   1999 |     7 |     6415.25500000 |   2931.40 |  3859.54 |  8772.65 |
| Men        |   1999 |     3 |     7469.45500000 |   3997.96 |  4454.62 |  4856.96 |
| Men        |   1999 |     4 |     7536.16750000 |   4070.53 |  4616.46 |  5193.62 |
| Children   |   1999 |     4 |     7658.07166667 |   4205.54 |  3665.09 |  3960.48 |
| Women      |   1999 |     6 |     6735.32500000 |   3289.16 |  3810.92 |  3421.56 |
| Women      |   1999 |     4 |     6473.30083333 |   3029.66 |  4066.84 |  4452.18 |
| Women      |   1999 |     4 |     6030.88583333 |   2590.14 |  4099.64 |  3348.41 |
| Children   |   1999 |     2 |     7658.07166667 |   4222.77 |  4822.93 |  3665.09 |
| Music      |   1999 |     6 |     7106.58916667 |   3672.15 |  4348.83 |  4135.57 |
| Shoes      |   1999 |     6 |     6846.16000000 |   3427.30 |  4007.11 |  4791.14 |
| Shoes      |   1999 |     7 |     6930.18416667 |   3513.24 |  4324.92 |  8995.99 |
| Women      |   1999 |     5 |     6575.87583333 |   3159.42 |  3352.17 |  3738.13 |
| Children   |   1999 |     3 |     6501.39916667 |   3088.82 |  4844.96 |  3211.37 |
| Shoes      |   1999 |     7 |     7151.23083333 |   3739.28 |  4889.14 | 10018.74 |
| Children   |   1999 |     6 |     6834.03666667 |   3438.01 |  3216.33 |  3933.33 |
| Men        |   1999 |     4 |     6092.52750000 |   2705.47 |  3580.93 |  3946.78 |
| Men        |   1999 |     1 |     7469.45500000 |   4084.37 | 15702.94 |  4454.62 |
| Shoes      |   1999 |     5 |     6930.18416667 |   3547.09 |  5277.39 |  4324.92 |
| Music      |   1999 |     7 |     6544.27083333 |   3169.16 |  3599.01 |  9674.19 |
| Music      |   1999 |     6 |     7665.45666667 |   4303.32 |  4646.23 |  6216.04 |
| Shoes      |   1999 |     2 |     7151.23083333 |   3794.46 |  4267.59 |  4220.40 |
| Music      |   1999 |     6 |     7305.85833333 |   3950.95 |  4897.65 |  4674.19 |
| Children   |   1999 |     1 |     6597.22500000 |   3245.57 | 13023.89 |  2872.68 |
| Music      |   1999 |     3 |     7014.37916667 |   3662.76 |  3877.52 |  4038.49 |
| Music      |   1999 |     2 |     5840.58750000 |   2490.51 |  3128.09 |  3883.31 |
| Men        |   1999 |     2 |     6092.52750000 |   2749.45 |  3614.00 |  3580.93 |
| Shoes      |   1999 |     6 |     7379.81500000 |   4039.47 |  4624.27 |  4832.28 |
| Children   |   1999 |     5 |     6550.79666667 |   3218.37 |  2541.51 |  4617.26 |
+------------+--------+-------+-------------------+-----------+----------+----------+
100 rows in set (6.86 sec)

[alberttwong]

Query Profile output
iceberg.log

[alberttwong]

Query profile warm
icebergwarm.log