![\"\u5c01\u9762\u4fe1\u606f\u56fe\"](\"https:\/\/www.wsisp.com\/helps\/wp-content\/uploads\/2026\/10\/20261010164104-6aca6aa0ac1f8.png\")
<\/p>\n<\/p>\n
\u5468\u4e09\u4e0b\u5348\u9700\u6c42\u8bc4\u5ba1\u4f1a，\u7a7a\u6c14\u91cc\u5f25\u6f2b\u7740\u7f8e\u5f0f\u5496\u5561\u548c\u706b\u836f\u5473\u3002\u8fd0\u8425\u8d1f\u8d23\u4eba\u628a\u4e00\u5f20\u5bc6\u5bc6\u9ebb\u9ebb\u6807\u4e86 160 \u591a\u4e2a\u5b57\u6bb5\u7684 Excel \u9700\u6c42\u8868\u62cd\u5728\u5c4f\u5e55\u4e0a：\u201c\u5927\u559c，\u6211\u4eec\u4e0d\u8981\u591a\u8868\u5173\u8054，\u4e5f\u4e0d\u8981\u641e\u90a3\u4e9b\u661f\u578b\u6a21\u578b\u3002\u4f60\u5c31\u7ed9\u6211\u505a\u4e00\u5f20\u5bbd\u8868，\u628a\u7528\u6237\u7684\u624b\u673a\u53f7\u3001\u6700\u8fd1\u4e00\u6b21\u4e0b\u5355\u65f6\u95f4\u3001\u6536\u8d27\u5730\u5740\u3001\u6d4f\u89c8\u7c7b\u76ee\u3001\u4f1a\u5458\u7b49\u7ea7\u3001\u53d1\u5238\u6838\u9500\u8bb0\u5f55\u3001\u751a\u81f3\u5ba2\u670d\u6295\u8bc9\u6807\u7b7e\u5168\u585e\u8fdb\u53bb，\u884c\u4e0d\u884c？\u6211\u4eec\u62c9\u6570\u636e\u8981\u81ea\u5df1\u81ea\u7531\u7b5b\u9009！\u201d<\/p>\n
\u5750\u5728\u6211\u65c1\u8fb9\u7684\u6570\u4ed3\u5efa\u6a21\u5de5\u7a0b\u5e08\u5c0f\u674e\u989d\u5934\u9752\u7b4b\u66b4\u8df3，\u8131\u53e3\u800c\u51fa：\u201c160 \u591a\u4e2a\u7ef4\u5ea6，\u7c92\u5ea6\u90fd\u4e0d\u4e00\u6837！\u8ba2\u5355\u662f\u4e8b\u4ef6\u7c92\u5ea6，\u6295\u8bc9\u662f\u5de5\u5355\u7c92\u5ea6，\u7528\u6237\u753b\u50cf\u662f\u65e5\u7ea7\u5feb\u7167\u3002\u5f3a\u884c\u5de6\u8fde（LEFT JOIN）\u6253\u5e73，\u4e00\u884c\u8ba2\u5355\u4ea7\u751f 5 \u6761\u5ba2\u670d\u8bb0\u5f55，\u6210\u4ea4\u989d\u76f4\u63a5\u88ab\u7ffb\u4e86\u4e94\u500d，\u98ce\u63a7\u6307\u6807\u5168\u4e71\u5957！\u8303\u5f0f\u5efa\u6a21\u3001\u7ef4\u5ea6\u5efa\u6a21\u7684\u7ecf\u5178\u539f\u5219\u4f60\u4eec\u662f\u4e00\u70b9\u4e0d\u770b\u554a！\u201d<\/p>\n
\u8fd0\u8425\u540c\u5b66\u6beb\u4e0d\u793a\u5f31：\u201c\u7ecf\u5178\u539f\u5219\u80fd\u5e2e\u6211\u8d76\u4e0a\u660e\u5929\u7684\u65e9\u62a5\u5417？\u6bcf\u6b21\u6211\u60f3\u4ea4\u53c9\u5206\u6790\u4e00\u4e2a\u65b0\u7ef4\u5ea6，\u63d0\u4e2a\u9700\u6c42\u5355\u7ed9\u4f60\u4eec，\u6392\u671f\u4e24\u5468，\u6392\u5230\u9ec4\u82b1\u83dc\u90fd\u51c9\u4e86！\u6211\u8981\u662f\u6709\u8fd9\u5f20\u5927\u5bbd\u8868，\u5728 Excel \u751a\u81f3\u62a5\u8868\u5de5\u5177\u91cc\u4e00\u62d6\u62fd\u5c31\u51fa\u6765\u4e86，\u8c01\u7231\u7b49\u4f60\u4eec\u6392\u671f\u554a！\u201d<\/p>\n
\u8fd9\u79cd\u5bf9\u8bdd\u5728\u51e0\u4e4e\u6bcf\u4e00\u5bb6\u4e92\u8054\u7f51\u516c\u53f8\u7684\u6570\u636e\u90e8\u95e8\u5468\u800c\u590d\u59cb\u5730\u4e0a\u6f14\u3002\u6570\u4ed3\u5de5\u7a0b\u5e08\u5949\u8303\u5f0f\u4e3a\u5723\u7ecf，\u4e1a\u52a1\u4eba\u5458\u89c6\u5927\u5bbd\u8868\u4e3a\u81ea\u7531\u795e\u5251\u3002 \u8fd9\u573a\u76f8\u7231\u76f8\u6740\u7684\u80cc\u540e，\u672c\u8d28\u4e0a\u662f\u201c\u6570\u4ed3\u6cbb\u7406\u6210\u672c\u3001\u5b58\u50a8\u8ba1\u7b97\u6027\u80fd\u201d\u4e0e\u201c\u4e1a\u52a1\u654f\u6377\u63a2\u7d22\u3001\u8ba4\u77e5\u8d1f\u8377\u201d\u4e4b\u95f4\u7684\u6df1\u523b\u4ee3\u6c9f\u3002<\/p>\n
\u7ad9\u5728\u4e1a\u52a1\u9700\u6c42\u7684\u89c6\u89d2，\u53bb\u6307\u8d23\u4ed6\u4eec\u201c\u4e0d\u61c2\u8303\u5f0f\u201d\u662f\u50b2\u6162\u4e14\u5f92\u52b3\u7684\u3002\u4e1a\u52a1\u65b9\u5bf9\u5927\u5bbd\u8868\u7684\u6e34\u6c42，\u6765\u81ea\u4e8e\u6781\u5176\u73b0\u5b9e\u7684\u75db\u70b9：<\/p>\n
\u5bf9\u4e8e\u8fd0\u8425\u3001\u5e02\u573a\u6216\u5546\u7ba1\u540c\u5b66\u6765\u8bf4，\u4ed6\u4eec\u7684\u5927\u8111\u6a21\u578b\u662f\u76f4\u89c2\u7684\u5b9e\u4f53（Entity）：\u4e00\u4e2a\u5177\u4f53\u7684\u7528\u6237，\u5728\u4e00\u4e2a\u5177\u4f53\u7684\u5546\u6237，\u4e70\u4e86\u4e00\u4ef6\u5177\u4f53\u7684\u5546\u54c1\u3002\u5982\u679c\u8981\u8ba9\u4ed6\u4eec\u5728\u5206\u6790\u65f6\u7406\u89e3 fact_order_detail\u3001dim_user_profile_df\u3001dim_merchant_scd2 \u4e4b\u95f4\u7684\u5185\u5916\u5173\u8054\u952e（Inner\/Outer Join Keys），\u4ee5\u53ca\u5904\u7406\u4e00\u5bf9\u591a\u81a8\u80c0\u5e26\u6765\u7684 GROUP BY \u805a\u5408\u5ea6\u91cf\u9664\u91cd\u95ee\u9898，\u76f8\u5f53\u4e8e\u903c\u8feb\u975e\u6280\u672f\u4eba\u5458\u5728\u8111\u4e2d\u8fd0\u884c\u4e00\u4e2a\u67e5\u8be2\u4f18\u5316\u5668（Query Optimizer）\u3002<\/p>\n
\u65e0\u8bba\u662f PowerBI\u3001Tableau，\u8fd8\u662f\u5404\u7c7b\u81ea\u7814\u7684\u6570\u636e\u770b\u677f\u5de5\u5177，\u5355\u8868\u62d6\u62fd\u7684\u4e0a\u624b\u95e8\u69db\u662f 0，\u800c\u591a\u8868\u8fde\u67e5\u7684\u95e8\u69db\u662f 80\u3002\u4e00\u65e6\u6d89\u53ca\u4e24\u4e2a\u4e8b\u5b9e\u8868\u7684\u6df7\u5408\u5206\u6790，\u4e1a\u52a1\u4eba\u5458\u62d6\u51fa\u7684\u770b\u677f 90% \u4f1a\u56e0\u4e3a\u5173\u8054\u6761\u4ef6\u7f3a\u5931\u51fa\u73b0\u7b1b\u5361\u5c14\u79ef，\u6700\u7ec8\u5bfc\u81f4\u62a5\u8868\u53e3\u5f84\u5168\u7ebf\u5931\u771f\u3002\u628a\u6240\u6709\u7ef4\u5ea6\u9884\u5148\u6253\u5e73\u6210\u4e00\u5f20\u5927\u5bbd\u8868，\u6210\u4e86\u4ed6\u4eec\u552f\u4e00\u80fd\u81ea\u4e3b\u638c\u63a7\u7684\u6570\u636e\u8d44\u4ea7\u3002<\/p>\n
\u5728\u6ca1\u6709\u7edf\u4e00\u5bbd\u8868\u524d，\u8fd0\u8425\u770b\u7684\u662f dwd_trade \u7b97\u51fa\u6765\u7684\u6210\u4ea4，\u8d22\u52a1\u770b\u7684\u662f\u7ed3\u7b97\u4e2d\u5fc3\u6d41\u6c34\u8868，\u98ce\u63a7\u770b\u7684\u662f\u5c65\u7ea6\u65e5\u5fd7\u3002\u4e09\u5f20\u8868\u53e3\u5f84\u5fae\u8c03\u4e00\u6b21，\u4e09\u4e2a\u90e8\u95e8\u8981\u5728\u5468\u4f1a\u4e0a\u4e3a 0.5% \u7684\u6570\u636e\u5dee\u5f02\u5435\u4e0a\u4e24\u4e2a\u5c0f\u65f6\u3002\u62e5\u6709\u4e00\u5f20\u201c\u6743\u5a01\u5927\u5bbd\u8868\u201d，\u6210\u4e86\u5927\u5bb6\u8fbe\u6210\u53e3\u5f84\u5171\u8bc6\u7684\u6700\u61d2\u60f0\u4f46\u4e5f\u6700\u6709\u6548\u7684\u624b\u6bb5\u3002<\/p>\n
\u4e1a\u52a1\u723d\u4e86，\u6570\u4ed3\u548c\u5e95\u5c42\u96c6\u7fa4\u5374\u5f00\u59cb\u5410\u8840\u3002\u628a\u6240\u6709\u7ef4\u5ea6\u548c\u5ea6\u91cf\u6beb\u65e0\u8282\u5236\u5730\u5e73\u94fa\u5230\u4e00\u5f20\u5355\u8868\u91cc，\u4f1a\u5f15\u53d1\u4e00\u7cfb\u5217\u81f4\u547d\u7684\u67b6\u6784\u9690\u60a3：<\/p>\n
+————————————————————-+
\n| \u4e1a\u52a1\u89c6\u89d2\u7684\u5927\u5bbd\u8868：\u7f8e\u597d\u3001\u81ea\u7531\u3001\u65e0\u62d8\u65e0\u675f\u7684\u5e73\u94fa\u4e16\u754c |
\n| [User] + [Order] + [Refund] + [Coupon] + [Logistics] + … |
\n+————————————————————-+
\n |
\n \u6280\u672f\u5b9e\u73b0\u7684\u73b0\u5b9e\u91cd\u51fb
\n v
\n+————————————————————-+
\n| 1. \u7c92\u5ea6\u81a8\u80c0\u4e0e\u5ea6\u91cf\u6c61\u67d3：1\u5bf9\u591a\u5173\u8054\u5f15\u53d1 SUM() \u865a\u9ad8 500% |
\n| 2. \u56de\u6eaf\u91cd\u5237\u5730\u72f1：\u4e0a\u6e38\u67d0\u4e2a\u53f6\u5b50\u7ef4\u8868\u91cd\u5237，\u5168\u5bbd\u8868\u767e TB \u91cd\u65b0\u5168\u91cf\u8dd1|
\n| 3. \u5b58\u50a8\u4e0e I\/O \u503e\u659c：\u5217\u5f0f\u5b58\u50a8\u5143\u6570\u636e\u7206\u70b8，\u5927\u91cf NULL \u7a00\u758f\u586b\u5145 |
\n| 4. \u8ba1\u7b97\u9501\u6b7b\u4e0e\u8d44\u6e90\u4e89\u62a2：\u5355\u6761 SELECT * \u62d6\u57ae Presto \/ Spark |
\n+————————————————————-+<\/p>\n
\u9762\u5bf9\u8fd9\u573a\u62c9\u952f\u6218，\u6210\u719f\u7684\u67b6\u6784\u5e08\u7edd\u4e0d\u80fd\u7b80\u5355\u5730\u201c\u5168\u7ed9\u201d\u6216\u201c\u5168\u62d2\u201d，\u800c\u662f\u901a\u8fc7\u67b6\u6784\u5206\u5c42\u5c06\u7269\u7406\u6a21\u578b\u4e0e\u903b\u8f91\u6d88\u8d39\u89e3\u8026\u3002<\/p>\n
\u575a\u51b3\u62d2\u7edd\u4e0d\u540c\u7c92\u5ea6\u4e8b\u5b9e\u8868\u7684\u5f3a\u884c\u6a2a\u5411\u7269\u7406\u62fc\u88c5\u3002<\/p>\n
— ClickHouse \u73b0\u4ee3\u5bbd\u8868\u6536\u655b\u8bbe\u8ba1：\u4f7f\u7528 Nested \u6570\u7ec4\u907f\u514d\u591a\u884c\u81a8\u80c0
\nCREATE TABLE dws_trade_order_wide_di
\n(
\n order_id UInt64,
\n user_id UInt64,
\n order_time DateTime,
\n pay_amount Decimal64(2),
\n province_id UInt16,
\n — \u5c06\u4f18\u60e0\u5238\u4f7f\u7528\u62c6\u89e3\u4e3a\u5d4c\u5957\u6570\u7ec4，\u62d2\u7edd\u6253\u5e73\u6210\u591a\u884c
\n coupons Nested(
\n coupon_id UInt64,
\n coupon_type String,
\n discount_amount Decimal64(2)
\n ),
\n — \u7269\u6d41\u6253\u6807\u76f4\u63a5\u805a\u5408\u4e3a\u8f7b\u91cf Set
\n logistics_status_list Array(String)
\n)
\nENGINE = MergeTree()
\nPARTITION BY toYYYYMMDD(order_time)
\nORDER BY (province_id, user_id, order_id);<\/p>\n
\u901a\u8fc7\u8bed\u4e49\u5c42（Semantic Layer \/ Headless BI），\u5c06\u591a\u8868\u5173\u8054\u903b\u8f91\u56fa\u5316\u5728\u5143\u6570\u636e\u5b57\u5178\u4e2d\u3002\u4e1a\u52a1\u4eba\u5458\u770b\u5230\u7684\u4f9d\u7136\u662f\u6982\u5ff5\u4e0a\u7684\u4e00\u6574\u5957\u7ef4\u5ea6\u6c60，\u4f46\u540e\u53f0\u901a\u8fc7\u52a8\u6001\u56fe\u8ba1\u7b97\u8fdb\u884c\u6309\u9700\u8fde\u67e5：<\/p>\n
from dataclasses import dataclass
\nfrom typing import List, Set<\/p>\n
@dataclass
\nclass SemanticField:
\n name: str
\n source_table: str
\n is_measure: bool
\n join_path: str = ""<\/p>\n
class DynamicSemanticRouter:
\n """\u8bed\u4e49\u8def\u7531：\u6309\u9700\u7ec4\u88c5\u6700\u5c0f\u4ee3\u4ef7\u7684 JOIN \u94fe\u8def，\u800c\u975e\u9884\u5236\u8d85\u5927\u7269\u7406\u5bbd\u8868"""
\n def __init__(self):
\n self.catalog = {
\n "order_amount": SemanticField("pay_amount", "dwd_trade_order", True),
\n "user_province": SemanticField("province_name", "dim_user", False, "dim_user.user_id = dwd_trade_order.user_id"),
\n "complaint_count": SemanticField("complaint_id", "dwd_cs_complaint", True, "dwd_cs_complaint.order_id = dwd_trade_order.order_id")
\n }<\/p>\n
def generate_pushdown_sql(self, requested_fields: List[str]) -> str:
\n needed_tables: Set[str] = set()
\n joins: List[str] = []
\n selects: List[str] = []<\/p>\n
for f_name in requested_fields:
\n field = self.catalog[f_name]
\n needed_tables.add(field.source_table)
\n selects.append(f"{field.source_table}.{field.name}")
\n if field.join_path and field.join_path not in joins:
\n joins.append(f"LEFT JOIN {field.source_table} ON {field.join_path}")<\/p>\n
# \u59cb\u7ec8\u4ee5\u6838\u5fc3\u4ea4\u6613\u4e8b\u5b9e\u8868\u4e3a\u57fa\u5ea7，\u6309\u9700\u6269\u5c55\u5916\u8fde
\n sql = f"SELECT {', '.join(selects)}\\\\nFROM dwd_trade_order\\\\n"
\n if joins:
\n sql += "\\\\n".join(joins)
\n return sql<\/p>\n
\u5468\u4e09\u4e0b\u5348\u9700\u6c42\u8bc4\u5ba1\u4f1a，\u7a7a\u6c14\u91cc\u5f25\u6f2b\u7740\u7f8e\u5f0f\u5496\u5561\u548c\u706b\u836f\u5473\u3002\u8fd0\u8425\u8d1f\u8d23\u4eba\u628a\u4e00\u5f20\u5bc6\u5bc6\u9ebb\u9ebb\u6807\u4e86 160 \u591a\u4e2a\u5b57\u6bb5\u7684 Excel \u9700\u6c42\u8868\u62cd\u5728\u5c4f\u5e55\u4e0a：\u201c\u5927\u559c，\u6211\u4eec\u4e0d\u8981\u591a\u8868\u5173\u8054，\u4e5f\u4e0d\u8981\u641e\u90a3\u4e9b\u661f\u578b\u6a21\u578b\u3002\u4f60\u5c31\u7ed9\u6211\u505a\u4e00\u5f20\u5bbd\u8868，\u628a\u7528\u6237\u7684\u624b\u673a\u53f7\u3001\u6700\u8fd1\u4e00\u6b21\u4e0b\u5355\u65f6\u95f4\u3001\u6536\u8d27\u5730\u5740\u3001\u6d4f\u89c8\u7c7b\u76ee\u3001\u4f1a\u5458\u7b49\u7ea7\u3001\u53d1\u5238\u6838\u9500\u8bb0\u5f55\u3001\u751a\u81f3\u5ba2\u670d\u6295\u8bc9\u6807\u7b7e\u5168\u585e\u8fdb\u53bb，\u884c\u4e0d\u884c？\u6211\u4eec\u62c9\u6570\u636e\u8981\u81ea\u5df1\u81ea\u7531\u7b5b\u9009！\u201d
\n\u5750\u5728\u6211\u65c1\u8fb9\u7684\u6570\u4ed3\u5efa\u6a21\u5de5\u7a0b\u5e08\u5c0f<\/p>\n","protected":false},"author":2,"featured_media":115044,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[66,81,50,292,51],"topic":[],"class_list":["post-115045","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-server","tag-ai","tag-python","tag-50","tag-292","tag-51"],"yoast_head":"\n