marketing_fa_302 60w数据
marketing_m_302 40W数据
取两表的差集
语句:
SELECT marketing_fa_302.mobile,marketing_fa_302.one_id
FROM marketing_fa_302
LEFT JOIN marketing_m_302 ON marketing_fa_302.mobile = marketing_m_302.mobile
WHERE marketing_m_302.mobile IS NULL and marketing_fa_302.mobile is not null;
提取crm_msg_log_302表中json字段req_content中的data_list集合中的数据值
json示例:
{
"traceNo": "5315c5e7-ec5d-4ea9-9980-4aea42386b4d",
"send_platform": "DcsW_sms",
"oper": "DisWC_regar",
"content": "点击 i3z.cc/v-ria88 购买36元季卡券获得价值55.5元四件产品,可享3个月尊享权益,拒收请回复R",
"req_time": "20240413100007",
"data_list": ["1819812311x", "1576166911x"],
"sign": "5b1763834b53723fa25505da98800799",
"linchpin_id": "210004JOB28462069",
"linchpin_name": "营销任务-提醒使用季卡"
}
语句:
SELECT json_array_elements_text((req_content::json)->'data_list') AS mobile
FROM crm_msg_log_302;