how to avoid long sql in clickhouse

Question

As ClickHouse can run complex SQL efficiently, we now write hundred-lines-in-one SQL to get the real time analysis results(join lots of source table). And it brings a problem is that it's so hard to maintain and reuse.

And we are using mybatis to write the sql in our Java Application. We try to use Mybatis's global parameter to define some sql for reuse, but not helps a lot in such huge lines SQL.

Is there any way to avoid such long sql or make the sql more readable and make this work more engineerable?

<select id="brandAnalysis" resultType="java.util.HashMap">
        SELECT gmvRank.id_code,
            gmvRank.rank,
            gmvRank.lastRank,
            gmvRank.gmv as sale_money,
            member.dealerMembers as dealer_turnover,
            member.teamMembers,
            case
                when member.dealerMembers = 0 then 0
                when member.teamMembers = 0 then 0
                else member.dealerMembers/member.teamMembers end AS dealer_turnover_active
        FROM (
            SELECT id_code,
            <choose>
                <when test="monthQuarter == 3">
                    case
                        when max(`quarter`) = #{quarter} then argMax(${sqlRankField},`month`)
                        else null end as rank,
                    case
                        when max(`quarter`) = #{quarter} then argMax(${sqlGmvField},`month`)
                        else null end as gmv,
                    case
                        when min(`quarter`) = #{compareQuarter} then argMin(${sqlRankField},`month`)
                        else null end as lastRank
                </when>
                <otherwise>
                    case
                        when max(`month`) = #{month} then argMax(${sqlRankField},`month`)
                        else null end as rank,
                    case
                        when max(`month`) = #{month} then argMax(${sqlGmvField},`month`)
                        else null end as gmv,
                    case
                        when min(`month`) = #{compareMonth} then argMin(${sqlRankField},`month`)
                        else null end as lastRank
                </otherwise>
            </choose>
            FROM
                tengju.dws_crm_brand_analysis_rank_dd_m
            <choose>
                <when test="monthQuarter == 3">
                    PREWHERE quarter in (#{quarter},#{compareQuarter})
                    and insert_time in (
                        SELECT MAX(insert_time)
                        FROM tengju.dws_crm_brand_analysis_rank_dd_m
                        PREWHERE quarter in (#{quarter},#{compareQuarter})
                        GROUP BY quarter
                    )
                </when>
                <otherwise>
                    PREWHERE month in (#{month},#{compareMonth})
                    and insert_time in (
                        SELECT MAX(insert_time)
                        FROM tengju.dws_crm_brand_analysis_rank_dd_m
                        PREWHERE month in (#{month},#{compareMonth})
                        GROUP BY month
                    )
                </otherwise>
            </choose>
            <if test="dominationIdCode != null and dominationLevel != null">
                and id_code in (
                    SELECT id_code
                    FROM tengju.dwd_user_domination_relation_map_all
                    PREWHERE visit_date >= formatDateTime(yesterday(),'%F')
                    AND insert_time in (
                        SELECT max(insert_time)
                        FROM tengju.dwd_user_domination_relation_map_all
                        PREWHERE visit_date >= formatDateTime(yesterday(),'%F')
                    )
                    AND domination_star_level = #{dominationLevel}
                    AND domination_id_code = #{dominationIdCode}
                    <if test="starLevels != null and starLevels.size() > 0">
                        AND star_level in
                        <foreach collection="starLevels" item="item" index="index" open="(" close=")" separator=",">
                            #{item}
                        </foreach>
                    </if>
                    <if test="isDirect != null and isDirect > 0">
                        AND parent_id_code = #{dominationIdCode}
                    </if>
                )
            </if>
            <if test="idCodes != null and idCodes.size() > 0">
                AND id_code in
                <foreach collection="idCodes" item="item" index="index" open="(" close=")" separator=",">
                    #{item}
                </foreach>
            </if>

            AND brand_id = #{brandId}
            GROUP BY id_code
        ) as gmvRank
        GLOBAL LEFT JOIN (
            <choose>
                <when test="crmRankCrowdPerspective == 4">
                    <include refid="smallTeamDealer" />
                </when>
                <when test="crmRankCrowdPerspective == 1">
                    <include refid="KATeamDealer" />
                </when>
                <otherwise>
                    <include refid="ecOrCityTeamDealer" />
                </otherwise>
            </choose>
        ) as member
        on (gmvRank.id_code = member.id_code)
        where 1=1
        <if test="saleMoneyFrom != null">
            AND gmvRank.gmv >= toDecimal128(#{saleMoneyFrom},2)
        </if>
        <if test="saleMoneyTo != null">
            AND toDecimal128(#{saleMoneyTo},2) >= gmvRank.gmv
        </if>
        order by ${sortType}
        LIMIT #{offset},#{pageSize}




  </select>

Answer 1

User common table expressions to get rid of nested operations. Each make and version of database server has its own style of SQL. Not all have common table expressions. All have views though. SQL queries can be insanely verbose, for sure there is no other option.