LongArticleTaskServer/app/recommend/offline_recommend/strategy/get_top_article.py

from .base import BaseStrategy


class GetTopArticleStrategy(BaseStrategy):
    @staticmethod
    def base() -> str:
        query = """
            SELECT title, sum(view_count) as total_view_count, sum(fans) as total_fan_count
            FROM datastat_sort_strategy
            WHERE position = 1 and gh_id = %s AND date_str >= '20250501' AND view_count > 1000
            GROUP BY title
            ORDER BY sum(view_count) / sum(fans) DESC
            LIMIT 25;
        """
        return query

    @staticmethod
    def strategy_v1(account_name: str, uid_cnt_threshold: int = 100) -> str:
        odps_query = f"""
            SELECT  title
                    ,COUNT(1) AS uid_cnt
            FROM    article_union_id_mapper
            WHERE   union_id IN (
                        SELECT  union_id
                        FROM    gzh_fans_info
                        WHERE   account_name = '{account_name}'
                        AND     dt = MAX_PT('gzh_fans_info')
                    )
            AND     accountname != '{account_name}'
            GROUP BY title
            HAVING  uid_cnt > {uid_cnt_threshold};
        """
        return odps_query