Superset + Trino 跨数据源联合查询方案

多组条件 → 取并集 (UNION)
├── 条件组1 → 用户表条件 ∩ 会员状态条件 (INTERSECT)
├── 条件组2 → 用户表条件 ∩ 会员状态条件 (INTERSECT)
└── ...

zhl_members              # 用户基础信息表
├── id                   # 用户ID (uid)
├── phone                # 手机号
├── create_time          # 注册时间
└── regist_success       # 注册成功标识 (1=成功)

zhl_member_profile       # 用户详细信息表
├── uid                  # 用户ID
├── grade                # 年级
├── birth_province_code  # 省份编码
├── birth_city_code      # 城市编码
└── birth_area_code      # 地区编码

zhl_member_book_mapping  # 用户业务绑定表
├── uid                  # 用户ID
└── business_id          # 业务ID

zhl_member_scopes        # 用户范围表(学校)
├── uid                  # 用户ID
└── scope_id             # 范围ID

sby_user_member          # 用户会员信息表
├── uid                  # 用户ID
├── product_id           # 产品ID
├── member_type          # 会员类型 (1=VIP, 2=SVIP, 3=SVIP+)
├── begin_time           # 生效开始时间
└── after_expiration_time # 到期时间

connector.name=mysql
connection-url=jdbc:mysql://user-db-host:3306/zhl_ucenter
connection-user=trino_user
connection-password=trino_password

connector.name=mysql
connection-url=jdbc:mysql://member-db-host:3306/zhl_recharge
connection-user=trino_user
connection-password=trino_password

SQLAlchemy URI: trino://user:password@trino-host:8080/hive

-- ================================================
-- 用户条件查询 - Trino 跨数据源联合查询（优化版）
-- ================================================
-- 参数说明：
--   business_id: 业务ID（必填）
--   product_id: 产品ID（必填）
--   region_type: 区域类型 (1-省份 2-城市 3-地区 4-学校)
--   region_ids: 地区ID列表，逗号分隔（Jinja 自动加引号）
--   grades: 年级列表，逗号分隔
--   register_start_time: 注册开始时间（Unix时间戳）
--   register_end_time: 注册结束时间（Unix时间戳）
--   phone_numbers: 手机号列表，逗号分隔（Jinja 自动加引号）
--   member_statuses: 会员状态列表，逗号分隔
--   page_no: 页码（从1开始）
--   page_size: 每页数量

{% set business_id = business_id or 1 %}
{% set product_id = product_id or 1 %}
{% set region_type = region_type or 1 %}
{% set region_ids = region_ids or '350000' %}
{% set grades = grades or '1,2' %}
{% set register_start_time = register_start_time or 0 %}
{% set register_end_time = register_end_time or 0 %}
{% set phone_numbers = phone_numbers or '18285664817' %}
{% set member_statuses = member_statuses or '1,2' %}
{% set page_no = page_no or 1 %}
{% set page_size = page_size or 100 %}


{# 将逗号分隔的字符串转换为带引号的 SQL IN 格式 #}
{% macro quote_list(value) %}
    {% if value and value != '' %}
        {% set items = value.split(',') %}
        {% for item in items %}
            '{{ item.strip() }}'
            {% if not loop.last %},{% endif %}
        {% endfor %}
    {% endif %}
{% endmacro %}


SELECT DISTINCT m.id AS uid
FROM test_sby_ucenter.sby_ucenter.zhl_member_book_mapping mbm
        INNER JOIN test_sby_ucenter.sby_ucenter.zhl_members m ON mbm.uid = m.id
        LEFT JOIN test_sby_ucenter.sby_ucenter.zhl_member_profile mp ON m.id = mp.uid
        LEFT JOIN test_sby_recharge_center.sby_recharge_center.sby_user_member um
                  ON m.id = um.uid
                     AND um.product_id = {{ product_id }}
   AND um.begin_time <= to_unixtime(now())
WHERE mbm.business_id = {{ business_id }}
  AND m.regist_success = 1
    {# 地区条件 #}
    {% if region_type > 0 and region_ids != '' %}
  AND (
    {% if region_type == 1 %}
   mp.birth_province_code IN ({{ quote_list(region_ids) }})
    {% elif region_type == 2 %}
   mp.birth_city_code IN ({{ quote_list(region_ids) }})
    {% elif region_type == 3 %}
   mp.birth_area_code IN ({{ quote_list(region_ids) }})
    {% elif region_type == 4 %}
   mp.school_id IN ({{ region_ids }})
    {% endif %}
   )
    {% endif %}
    {# 年级条件 #}
    {% if grades != '' %}
  AND mp.grade IN ({{ grades }})
    {% endif %}
    {# 注册时间条件 #}
    {% if register_start_time > 0 %}
  AND m.create_time >= {{ register_start_time }}
    {% endif %}
    {% if register_end_time > 0 %}
  AND m.create_time <= {{ register_end_time }}
    {% endif %}
    {# 手机号条件 #}
    {% if phone_numbers != '' %}
  AND m.phone IN ({{ quote_list(phone_numbers) }})
    {% endif %}
    {# 会员状态条件 #}
    {% if member_statuses != '' %}
    {% set statuses = member_statuses.split(',') %}
   AND (
   {# 非会员条件（与其他条件是 OR 关系）#}
        {% if '1' in statuses %}
        (um.after_expiration_time IS NULL OR um.after_expiration_time <= to_unixtime(now()))
            {% if statuses|length > 1 %}
            OR
            {% endif %}
        {% endif %}
        {# 会员类型条件: 2=VIP, 3=SVIP, 4=SVIP+ #}
        {% if '2' in statuses or '3' in statuses or '4' in statuses %}
        (
            um.after_expiration_time > to_unixtime(now())
            AND um.member_type IN (
                {% if '2' in statuses %}1{% endif %}
                {% if '3' in statuses %}{% if '2' in statuses %},{% endif %}2{% endif %}
                {% if '4' in statuses %}{% if '2' in statuses or '3' in statuses %},{% endif %}3{% endif %}
            )
        )
            {% if '5' in statuses or '6' in statuses or '7' in statuses or '8' in statuses or '9' in statuses or '10' in statuses %}
            OR
            {% endif %}
        {% endif %}
        {# 即将到期条件: 5=3天, 6=7天, 7=30天 #}
        {% if '5' in statuses %}
        (um.after_expiration_time > to_unixtime(now()) AND um.after_expiration_time <= to_unixtime(now()) + 259200)
            {% if '6' in statuses or '7' in statuses or '8' in statuses or '9' in statuses or '10' in statuses %}
            OR
            {% endif %}
        {% endif %}
        {% if '6' in statuses %}
        (um.after_expiration_time > to_unixtime(now()) AND um.after_expiration_time <= to_unixtime(now()) + 604800)
            {% if '7' in statuses or '8' in statuses or '9' in statuses or '10' in statuses %}
            OR
            {% endif %}
        {% endif %}
        {% if '7' in statuses %}
        (um.after_expiration_time > to_unixtime(now()) AND um.after_expiration_time <= to_unixtime(now()) + 2592000)
            {% if '8' in statuses or '9' in statuses or '10' in statuses %}
            OR
            {% endif %}
        {% endif %}
        {# 已过期条件: 8=3天, 9=7天, 10=30天 #}
        {% if '8' in statuses %}
        (um.after_expiration_time <= to_unixtime(now()) AND um.after_expiration_time > to_unixtime(now()) - 259200)
            {% if '9' in statuses or '10' in statuses %}
            OR
            {% endif %}
        {% endif %}
        {% if '9' in statuses %}
        (um.after_expiration_time <= to_unixtime(now()) AND um.after_expiration_time > to_unixtime(now()) - 604800)
            {% if '10' in statuses %}
            OR
            {% endif %}
        {% endif %}
        {% if '10' in statuses %}
        (um.after_expiration_time <= to_unixtime(now()) AND um.after_expiration_time > to_unixtime(now()) - 2592000)
        {% endif %}
    )
    {% endif %}
ORDER BY m.id
   LIMIT {{ page_size }} OFFSET {{ (page_no - 1) * page_size }}

{
  "database_id": 1,
  "sql": "-- 从 Dataset 复制或预定义的 SQL 模板",
  "template_params": "{\"business_id\":1,\"product_id\":1,\"member_statuses\":\"2,3\"}",
  "limit": 100
}