基于阿里云RDS SQL Server + 函数计算 + 通义AI构建智能销售分析平台Demo
= '2016-01-01' \\\n GROUP BY si.StockItemID, si.StockItemName, si.SearchDetails, \\\n sc.ColorName, si.Brand, si.Size \\\n HAVING SUM(ol.Quantity * ol.UnitPrice) > 0 \\\n ORDER BY Revenue DESC)\n SELECT * \\\n FROM TopProducts; \\\n \"\"\"\n\n# 2. 商品关联分析查询（购物篮分析）\n# 用途：分析哪些商品经常一起被购买，用于推荐系统和交叉销售\nPRODUCT_AFFINITY_ANALYSIS_QUERY = \"\"\"\n -- 商品关联分析查询（购物篮分析）\n-- 分析哪些商品经常一起被购买\n WITH ProductAffinityAnalysis AS (SELECT ol1.StockItemID AS Product1, \\\n ol2.StockItemID AS Product2, \\\n p1.StockItemName AS Product1Name, \\\n p2.StockItemName AS Product2Name, \\\n COUNT(*) AS CoOccurrence, \\\n COUNT(*) * 1.0 / \\\n (SELECT COUNT(DISTINCT OrderID) \\\n FROM Sales.OrderLines \\\n WHERE StockItemID = ol1.StockItemID) AS SupportScore \\\n FROM Sales.OrderLines ol1 \\\n INNER JOIN Sales.OrderLines ol2 \\\n ON ol1.OrderID = ol2.OrderID AND ol1.StockItemID < ol2.StockItemID \\\n INNER JOIN Warehouse.StockItems p1 ON ol1.StockItemID = p1.StockItemID \\\n INNER JOIN Warehouse.StockItems p2 ON ol2.StockItemID = p2.StockItemID \\\n INNER JOIN Sales.Orders o ON ol1.OrderID = o.OrderID \\\n WHERE o.OrderDate >= '2016-01-01' \\\n GROUP BY ol1.StockItemID, ol2.StockItemID, \\\n p1.StockItemName, p2.StockItemName \\\n HAVING COUNT(*) >= 3 -- 至少同时出现3次\n )\n SELECT TOP 20 \n Product1Name, Product2Name,\n CoOccurrence,\n SupportScore\n FROM ProductAffinityAnalysis\n ORDER BY SupportScore DESC; \\\n \"\"\"\n\n# 3. 库存状态分析查询\n# 用途：分析当前库存状态和商品需求，识别库存风险\nINVENTORY_STATUS_ANALYSIS_QUERY = \"\"\"\n -- 库存状态分析查询\n-- 分析当前库存状态和商品需求\n SELECT TOP 30\n si.StockItemID, si.StockItemName,\n si.Brand,\n si.Size,\n ISNULL(sih.QuantityOnHand, 0) as CurrentStock,\n ISNULL(sih.LastCostPrice, 0) as LastCostPrice,\n COUNT(ol.OrderLineID) as OrderFrequency,\n AVG(ol.Quantity) AS AvgDemand,\n SUM(ol.Quantity * ol.UnitPrice) as TotalRevenue,\n CASE\n WHEN ISNULL(sih.QuantityOnHand, 0) = '2016-01-01' \\\n GROUP BY c.CustomerID, c.CustomerName, \\\n c.DeliveryAddressLine1, c.DeliveryAddressLine2)\n SELECT TOP 20\n CustomerName, DeliveryAddressLine2 as Region,\n OrderCount,\n TotalSpent,\n AvgOrderValue,\n UniqueProductsPurchased,\n CASE\n WHEN TotalSpent > 50000 THEN 'VIP'\n WHEN TotalSpent > 20000 THEN 'Premium'\n WHEN TotalSpent > 5000 THEN 'Regular'\n ELSE 'Basic'\n END AS CustomerTier,\n CASE\n WHEN DaysSinceLastOrder = '2016-01-01'\n GROUP BY si2.StockItemName\n ORDER BY SUM (ol2.Quantity * ol2.UnitPrice) DESC\n )\n GROUP BY MONTH (o.OrderDate), DATENAME(MONTH, o.OrderDate), si.StockItemName\n ORDER BY OrderMonth, MonthlyRevenue DESC; \\\n \"\"\""},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"# 1. 热销商品分析查询\n# 用途：显示销售额最高的商品及其详细信息，包括收入、客户数量、平均价格等\nTOP_SELLING_PRODUCTS_QUERY = \"\"\"\n -- 热销商品分析查询（已验证可用）\n-- 显示销售额最高的商品及其详细信息\n WITH TopProducts AS (SELECT TOP 10\n si.StockItemName, si.SearchDetails, \\\n ISNULL(sc.ColorName, 'Default') as ColorName, \\\n si.Brand, \\\n si.Size, \\\n SUM(ol.Quantity * ol.UnitPrice) as Revenue, \\\n COUNT(DISTINCT o.CustomerID) as CustomerCount, \\\n AVG(ol.UnitPrice) as AvgPrice, \\\n SUM(ol.Quantity) as TotalQuantitySold \\\n FROM Warehouse.StockItems si \\\n LEFT JOIN Sales.OrderLines ol ON si.StockItemID = ol.StockItemID \\\n LEFT JOIN Sales.Orders o ON ol.OrderID = o.OrderID \\\n LEFT JOIN Warehouse.Colors sc ON si.ColorID = sc.ColorID \\\n WHERE o.OrderDate >= '2016-01-01' \\\n GROUP BY si.StockItemID, si.StockItemName, si.SearchDetails, \\\n sc.ColorName, si.Brand, si.Size \\\n HAVING SUM(ol.Quantity * ol.UnitPrice) > 0 \\\n ORDER BY Revenue DESC)\n SELECT * \\\n FROM TopProducts; \\\n \"\"\"\n\n# 2. 商品关联分析查询（购物篮分析）\n# 用途：分析哪些商品经常一起被购买，用于推荐系统和交叉销售\nPRODUCT_AFFINITY_ANALYSIS_QUERY = \"\"\"\n -- 商品关联分析查询（购物篮分析）\n-- 分析哪些商品经常一起被购买\n WITH ProductAffinityAnalysis AS (SELECT ol1.StockItemID AS Product1, \\\n ol2.StockItemID AS Product2, \\\n p1.StockItemName AS Product1Name, \\\n p2.StockItemName AS Product2Name, \\\n COUNT(*) AS CoOccurrence, \\\n COUNT(*) * 1.0 / \\\n (SELECT COUNT(DISTINCT OrderID) \\\n FROM Sales.OrderLines \\\n WHERE StockItemID = ol1.StockItemID) AS SupportScore \\\n FROM Sales.OrderLines ol1 \\\n INNER JOIN Sales.OrderLines ol2 \\\n ON ol1.OrderID = ol2.OrderID AND ol1.StockItemID < ol2.StockItemID \\\n INNER JOIN Warehouse.StockItems p1 ON ol1.StockItemID = p1.StockItemID \\\n INNER JOIN Warehouse.StockItems p2 ON ol2.StockItemID = p2.StockItemID \\\n INNER JOIN Sales.Orders o ON ol1.OrderID = o.OrderID \\\n WHERE o.OrderDate >= '2016-01-01' \\\n GROUP BY ol1.StockItemID, ol2.StockItemID, \\\n p1.StockItemName, p2.StockItemName \\\n HAVING COUNT(*) >= 3 -- 至少同时出现3次\n )\n SELECT TOP 20 \n Product1Name, Product2Name,\n CoOccurrence,\n SupportScore\n FROM ProductAffinityAnalysis\n ORDER BY SupportScore DESC; \\\n \"\"\"\n\n# 3. 库存状态分析查询\n# 用途：分析当前库存状态和商品需求，识别库存风险\nINVENTORY_STATUS_ANALYSIS_QUERY = \"\"\"\n -- 库存状态分析查询\n-- 分析当前库存状态和商品需求\n SELECT TOP 30\n si.StockItemID, si.StockItemName,\n si.Brand,\n si.Size,\n ISNULL(sih.QuantityOnHand, 0) as CurrentStock,\n ISNULL(sih.LastCostPrice, 0) as LastCostPrice,\n COUNT(ol.OrderLineID) as OrderFrequency,\n AVG(ol.Quantity) AS AvgDemand,\n SUM(ol.Quantity * ol.UnitPrice) as TotalRevenue,\n CASE\n WHEN ISNULL(sih.QuantityOnHand, 0) = '2016-01-01' \\\n GROUP BY c.CustomerID, c.CustomerName, \\\n c.DeliveryAddressLine1, c.DeliveryAddressLine2)\n SELECT TOP 20\n CustomerName, DeliveryAddressLine2 as Region,\n OrderCount,\n TotalSpent,\n AvgOrderValue,\n UniqueProductsPurchased,\n CASE\n WHEN TotalSpent > 50000 THEN 'VIP'\n WHEN TotalSpent > 20000 THEN 'Premium'\n WHEN TotalSpent > 5000 THEN 'Regular'\n ELSE 'Basic'\n END AS CustomerTier,\n CASE\n WHEN DaysSinceLastOrder = '2016-01-01'\n GROUP BY si2.StockItemName\n ORDER BY SUM (ol2.Quantity * ol2.UnitPrice) DESC\n )\n GROUP BY MONTH (o.OrderDate), DATENAME(MONTH, o.OrderDate), si.StockItemName\n ORDER BY OrderMonth, MonthlyRevenue DESC; \\\n \"\"\""]]],["p",{"uuid":"mdfzs3lxqw13uzf905g"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"示例结果如图"]]],["p",{"uuid":"mdfznoprsa9p0zwt97","ind":{}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]],["img",{"uuid":"mdgrbczkd6qtoneoolo","name":"image.png","size":959572,"width":748,"height":440.6534653465347,"src":"https://oss-ata.alibaba.com/article/2025/07/b71dceca-5db6-4091-b316-d60dc270e40b.png","extraData":{"resourceId":"337303a0-5a22-4d62-a3bc-7361cf4ae0a0","metaData":{"size":959572,"originWidth":1616,"originHeight":952,"format":"png","ratio":1}}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["p",{"uuid":"mdfzs1zneiq51axp97m"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["h1",{"uuid":"mdg08inknmu1zszousi","spacing":{"before":26.666666666666664,"after":9,"line":0.8529411764705882}},["span",{"data-type":"text"},["span",{"bold":true,"sz":20,"szUnit":"pt","data-type":"leaf"},"配置大模型访问"]]],["p",{"uuid":"mdg0fqqbe4m1ntkfdwh","ind":{"firstLine":32}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"进入阿里云百炼控制台"]],["a",{"href":"https://bailian.console.aliyun.com/","uuid":"mdg0fw98dblswy0y1hb"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"https://bailian.console.aliyun.com/"]]],["span",{"data-type":"text"},["span",{"data-type":"leaf"}," ，获取用于调用大模型的API Key，这里我们的文本模式使用qwen-max，图片生成模型使用 wanx2.1-t2i-plus"]]],["p",{"uuid":"mdg0dxplwe1foia001","ind":{"firstLine":32}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]],["img",{"uuid":"mdgrbczkynw9vzalor","name":"image.png","size":833841,"width":608,"height":708,"src":"https://oss-ata.alibaba.com/article/2025/07/bae897a3-0303-48bc-81df-4b751224417b.png","extraData":{"resourceId":"ecac5c68-9835-4499-b40f-8f651ecdbd12","metaData":{"size":833841,"originWidth":1216,"originHeight":1416,"format":"png","ratio":1}}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["p",{"uuid":"mdg0kivpcgrpdu2713w"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["p",{"uuid":"mdg0lx9z6jo4aon1nsr","ind":{"firstLine":32}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"对于这5种销售分析场景，我们分别将提示词封装到类中，将SQL Server返回的数据配合固定的系统提示词与用户提示词传给代码，核心代码如下："]]],["code",{"syntax":"python","theme":"default","wrap":true,"id":"xkvuuv","title":"","fold":false,"hideHeader":false,"codeFolding":false,"showLineNumber":true,"font":{"fontFamily":"defaultFont","ligatures":false},"enableMacHeader":false,"uuid":"mdg0mxq43yt45esyljc","code":"class AIEnhance:\n \"\"\"封装对大语言模型的调用，用于数据分析\"\"\"\n\n def __init__(self, model='qwen-max', image_model='wanx2.1-t2i-plus'):\n self.model = model\n self.image_model = image_model\n\n\n def analyze_top_selling_products(self, products_data):\n system_prompt = \"你是一位资深的数据分析师。\"\n user_prompt = (\"请根据以下热销商品数据，生成一段简洁、深刻的分析报告。\\n\"\n \"报告应包括：\\n\"\n \"1. 总结关键的销售趋势。\\n\"\n \"2. 指出表现最突出的几款商品及其特点。\\n\"\n \"3. 基于数据，提出1-2条具体的商业建议。\")\n return self._execute_analysis_prompt(system_prompt, user_prompt, products_data)\n\n def analyze_product_affinity(self, affinity_data):\n system_prompt = \"你是一位零售策略师，擅长通过购物篮分析提升交叉销售。\"\n user_prompt = (\"请分析以下商品关联数据（哪些商品经常被一起购买）。\\n\"\n \"分析应包括：\\n\"\n \"1. 识别出关联性最强的几组商品（基于SupportScore和CoOccurrence）。\\n\"\n \"2. 提出具体的营销建议，如商品捆绑、组合折扣或在商品页面进行推荐。\\n\"\n \"3. 指出是否有任何非直观的、有趣的商品组合值得关注。\")\n return self._execute_analysis_prompt(system_prompt, user_prompt, affinity_data)\n\n def analyze_inventory(self, inventory_data):\n system_prompt = \"你是一位供应链和库存管理专家。\"\n user_prompt = (\"请根据下面的库存状态和销售数据进行分析。\\n\"\n \"分析应包括：\\n\"\n \"1. 识别出有风险的商品，特别是库存状态为'Critical'且订单频率(OrderFrequency)较高的商品，并建议立即采取行动。\\n\"\n \"2. 找出库存积压的商品（库存量大但订单频率低），并建议处理方式（如促销、清仓）。\\n\"\n \"3. 对整体库存健康状况给出一个简要评价。\")\n return self._execute_analysis_prompt(system_prompt, user_prompt, inventory_data)\n\n def analyze_customer_behavior(self, customer_data):\n system_prompt = \"你是一位客户关系管理（CRM）与营销专家。\"\n user_prompt = (\"请分析以下客户行为和分层数据。\\n\"\n \"分析应包括：\\n\"\n \"1. 总结客户群体的构成（如VIP, Premium客户的占比和特点）。\\n\"\n \"2. 为不同客户状态('Active', 'At Risk', 'Inactive')的客户群体提出差异化的营销策略。\\n\"\n \"3. 描述高价值客户的核心特征，为精准营销提供依据。\")\n return self._execute_analysis_prompt(system_prompt, user_prompt, customer_data)\n\n def analyze_seasonal_trends(self, seasonal_data):\n system_prompt = \"你是一位市场分析师，精通销售预测和季节性趋势分析。\"\n user_prompt = (\"请分析以下热销商品的月度销售数据，识别季节性趋势。\\n\"\n \"分析应包括：\\n\"\n \"1. 指出哪些商品有明显的销售旺季和淡季。\\n\"\n \"2. 总结哪些月份是整体销售的高峰期和低谷期。\\n\"\n \"3. 基于这些季节性模式，为库存规划和营销活动提供具体建议。\")\n return self._execute_analysis_prompt(system_prompt, user_prompt, seasonal_data)"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"class AIEnhance:\n \"\"\"封装对大语言模型的调用，用于数据分析\"\"\"\n\n def __init__(self, model='qwen-max', image_model='wanx2.1-t2i-plus'):\n self.model = model\n self.image_model = image_model\n\n\n def analyze_top_selling_products(self, products_data):\n system_prompt = \"你是一位资深的数据分析师。\"\n user_prompt = (\"请根据以下热销商品数据，生成一段简洁、深刻的分析报告。\\n\"\n \"报告应包括：\\n\"\n \"1. 总结关键的销售趋势。\\n\"\n \"2. 指出表现最突出的几款商品及其特点。\\n\"\n \"3. 基于数据，提出1-2条具体的商业建议。\")\n return self._execute_analysis_prompt(system_prompt, user_prompt, products_data)\n\n def analyze_product_affinity(self, affinity_data):\n system_prompt = \"你是一位零售策略师，擅长通过购物篮分析提升交叉销售。\"\n user_prompt = (\"请分析以下商品关联数据（哪些商品经常被一起购买）。\\n\"\n \"分析应包括：\\n\"\n \"1. 识别出关联性最强的几组商品（基于SupportScore和CoOccurrence）。\\n\"\n \"2. 提出具体的营销建议，如商品捆绑、组合折扣或在商品页面进行推荐。\\n\"\n \"3. 指出是否有任何非直观的、有趣的商品组合值得关注。\")\n return self._execute_analysis_prompt(system_prompt, user_prompt, affinity_data)\n\n def analyze_inventory(self, inventory_data):\n system_prompt = \"你是一位供应链和库存管理专家。\"\n user_prompt = (\"请根据下面的库存状态和销售数据进行分析。\\n\"\n \"分析应包括：\\n\"\n \"1. 识别出有风险的商品，特别是库存状态为'Critical'且订单频率(OrderFrequency)较高的商品，并建议立即采取行动。\\n\"\n \"2. 找出库存积压的商品（库存量大但订单频率低），并建议处理方式（如促销、清仓）。\\n\"\n \"3. 对整体库存健康状况给出一个简要评价。\")\n return self._execute_analysis_prompt(system_prompt, user_prompt, inventory_data)\n\n def analyze_customer_behavior(self, customer_data):\n system_prompt = \"你是一位客户关系管理（CRM）与营销专家。\"\n user_prompt = (\"请分析以下客户行为和分层数据。\\n\"\n \"分析应包括：\\n\"\n \"1. 总结客户群体的构成（如VIP, Premium客户的占比和特点）。\\n\"\n \"2. 为不同客户状态('Active', 'At Risk', 'Inactive')的客户群体提出差异化的营销策略。\\n\"\n \"3. 描述高价值客户的核心特征，为精准营销提供依据。\")\n return self._execute_analysis_prompt(system_prompt, user_prompt, customer_data)\n\n def analyze_seasonal_trends(self, seasonal_data):\n system_prompt = \"你是一位市场分析师，精通销售预测和季节性趋势分析。\"\n user_prompt = (\"请分析以下热销商品的月度销售数据，识别季节性趋势。\\n\"\n \"分析应包括：\\n\"\n \"1. 指出哪些商品有明显的销售旺季和淡季。\\n\"\n \"2. 总结哪些月份是整体销售的高峰期和低谷期。\\n\"\n \"3. 基于这些季节性模式，为库存规划和营销活动提供具体建议。\")\n return self._execute_analysis_prompt(system_prompt, user_prompt, seasonal_data)"]]],["p",{"uuid":"mdg0mvu3jipf2s0750r","ind":{}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["h1",{"uuid":"mdg0o6j9gg1084n4a3d","ind":{},"spacing":{"before":26.666666666666664,"after":9,"line":0.8529411764705882}},["span",{"data-type":"text"},["span",{"bold":true,"sz":20,"szUnit":"pt","data-type":"leaf"},"配置函数计算"]]],["p",{"uuid":"mdg0t6osyn33bucnqjb","ind":{"firstLine":32}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"进入函数计算控制台"]],["a",{"href":"https://fcnext.console.aliyun.com/overview","uuid":"mdg0xt89tlpm5jgg1v"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"https://fcnext.console.aliyun.com/"]]],["span",{"data-type":"text"},["span",{"data-type":"leaf"}," ，创建函数"]]],["p",{"uuid":"mdg0yicg1rernxr4dak","ind":{}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]],["img",{"uuid":"mdgrbczkc65jnrf8agv","name":"image.png","size":628652,"width":748,"height":391.76500000000004,"src":"https://oss-ata.alibaba.com/article/2025/07/b8564d8a-0e34-4dcc-a76f-ad5b6cc0ec7e.png","extraData":{"resourceId":"c4c9d644-2cd5-4360-8f8d-92e34c726cea","metaData":{"size":628652,"originWidth":1604,"originHeight":840,"format":"png","ratio":1}}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["p",{"uuid":"mdgqulzzmknfwnx73","ind":{}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["p",{"uuid":"mdgqumubfzagpijw3bv","ind":{"firstLine":32}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"函数类型选择“任务函数”。"]]],["p",{"uuid":"mdg0yixf98w9ykuhfib"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["p",{"uuid":"mdg0ujehvzko8klcw7k","ind":{}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]],["img",{"uuid":"mdgrbczk4kqvpr0nlqa","name":"image.png","size":1386110,"width":748,"height":532.95,"src":"https://oss-ata.alibaba.com/article/2025/07/3bc6778c-40ae-4428-8f1e-438fbcb2dd5d.png","extraData":{"resourceId":"804d1c7d-3ea4-4d7f-9751-cafff4b77c0c","metaData":{"size":1386110,"originWidth":2136,"originHeight":1522,"format":"png","ratio":1}}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["p",{"uuid":"mdg5v0scdrclfpx4zt"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["h2",{"uuid":"mdg66092swcqoo3v2sq","spacing":{"before":21.333333333333332,"after":9,"line":0.8529411764705882}},["span",{"data-type":"text"},["span",{"bold":true,"sz":16,"szUnit":"pt","data-type":"leaf"},"部署代码"]]],["p",{"uuid":"mdgqwubcym2kxn9h8ml","ind":{"firstLine":32}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"创建完成后，部署代码，入口代码如下，通过解析简单的QueryString获取参数，通过不同的访问类别调用不同的SQL与大模型分析，典型的访问请求例如："]]],["p",{"uuid":"mdg6kw5qmgdbd0wfzs"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"https://自定义函数地址.cn-beijing.fcapp.run?analysis_type=product_affinity&ai_result=true&generate_image=true "]]],["code",{"syntax":"python","theme":"default","wrap":true,"id":"vyl1np","title":"","fold":false,"hideHeader":false,"codeFolding":false,"showLineNumber":true,"font":{"fontFamily":"defaultFont","ligatures":false},"enableMacHeader":false,"uuid":"mdg66hw94dywip6ves6","code":"import json\nimport logging\n\n# 配置日志\nlogger = logging.getLogger()\nlogger.setLevel(logging.INFO)\n\nfrom database_wrapper import WideWorldImportersDB\nfrom ai_enhance import create_ai_analyzer\n\ndb = WideWorldImportersDB()\nai_service = create_ai_analyzer()\n\n\ndef handler(event, context):\n \"\"\"\n 函数入口点 (事件驱动)\n Args:\n event (dict): 期望包含:\n - analysis_type: 分析类型 (必需)\n - ai_result: 是否需要AI分析 (可选，值为 'true' 时启用)\n - generate_image: 是否生成图片 (可选，值为 'true' 时启用，仅支持 product_affinity)\n\n Returns:\n dict: 包含原始数据或AI分析结果的字典\n \"\"\"\n\n # 解析事件数据\n if isinstance(event, bytes):\n event = json.loads(event.decode('utf-8'))\n elif isinstance(event, str):\n event = json.loads(event)\n\n # 获取分析类型参数\n analysis_type = event.get('analysis_type')\n if not analysis_type:\n query_params = event.get('queryParameters', {})\n analysis_type = query_params.get('analysis_type')\n\n if not analysis_type:\n return {\"status\": \"error\", \"message\": \"请求中缺少 'analysis_type' 参数\"}\n\n # 获取其他可选参数\n ai_result = event.get('ai_result') or event.get('queryParameters', {}).get('ai_result')\n generate_image = event.get('generate_image') or event.get('queryParameters', {}).get('generate_image')\n\n logging.info(f\"处理请求: analysis_type={analysis_type}, ai_result={ai_result}, generate_image={generate_image}\")\n\n try:\n # 获取原始数据\n raw_data = get_raw_data(analysis_type)\n if not raw_data:\n return {\"status\": \"error\", \"message\": f\"未找到 {analysis_type} 类型的数据\"}\n\n # 构建基础返回结果\n result = {\n \"status\": \"success\",\n \"analysis_type\": analysis_type,\n \"data_count\": len(raw_data),\n \"raw_data\": raw_data\n }\n\n # 如果需要AI分析结果\n if ai_result == 'true':\n logging.info(f\"开始执行AI分析: {analysis_type}\")\n analysis_result = get_ai_analysis(analysis_type, raw_data)\n result[\"ai_analysis\"] = analysis_result\n logging.info(\"AI分析完成\")\n\n # 如果需要生成图片（目前只支持商品关联分析）\n if generate_image == 'true':\n if analysis_type == 'product_affinity':\n logging.info(\"开始生成商品组合图片\")\n image_result = ai_service.generate_product_bundle_image(raw_data)\n result[\"generated_image\"] = image_result\n logging.info(\"图片生成完成\")\n else:\n result[\"image_warning\"] = f\"图片生成功能目前只支持 'product_affinity' 类型，当前类型为 '{analysis_type}'\"\n\n return result\n\n except Exception as e:\n error_msg = f\"处理 {analysis_type} 请求时发生错误: {str(e)}\"\n logging.error(error_msg)\n return {\"status\": \"error\", \"message\": error_msg}\n\n\ndef get_raw_data(analysis_type):\n \"\"\"\n 根据分析类型获取原始数据\n\n Args:\n analysis_type (str): 分析类型\n\n Returns:\n list: 原始数据列表，如果类型不支持则返回 None\n \"\"\"\n\n if analysis_type == 'top_selling_products':\n return db.get_top_selling_products(10)\n\n elif analysis_type == 'product_affinity':\n return db.get_product_affinity_analysis(min_occurrence=5, limit=15)\n\n elif analysis_type == 'inventory_status':\n return db.get_inventory_status_analysis(30)\n\n elif analysis_type == 'customer_behavior':\n return db.get_customer_behavior_analysis(20)\n\n elif analysis_type == 'seasonal_sales':\n return db.get_seasonal_sales_trend(10)\n\n else:\n logging.warning(f\"不支持的分析类型: {analysis_type}\")\n return None\n\n\ndef get_ai_analysis(analysis_type, data):\n \"\"\"\n 根据分析类型和数据获取AI分析结果\n\n Args:\n analysis_type (str): 分析类型\n data (list): 原始数据\n\n Returns:\n str: AI分析结果文本\n \"\"\"\n\n if analysis_type == 'top_selling_products':\n return ai_service.analyze_top_selling_products(data)\n\n elif analysis_type == 'product_affinity':\n return ai_service.analyze_product_affinity(data)\n\n elif analysis_type == 'inventory_status':\n return ai_service.analyze_inventory(data)\n\n elif analysis_type == 'customer_behavior':\n return ai_service.analyze_customer_behavior(data)\n\n elif analysis_type == 'seasonal_sales':\n return ai_service.analyze_seasonal_trends(data)\n\n else:\n return f\"不支持的分析类型: {analysis_type}\""},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"import json\nimport logging\n\n# 配置日志\nlogger = logging.getLogger()\nlogger.setLevel(logging.INFO)\n\nfrom database_wrapper import WideWorldImportersDB\nfrom ai_enhance import create_ai_analyzer\n\ndb = WideWorldImportersDB()\nai_service = create_ai_analyzer()\n\n\ndef handler(event, context):\n \"\"\"\n 函数入口点 (事件驱动)\n Args:\n event (dict): 期望包含:\n - analysis_type: 分析类型 (必需)\n - ai_result: 是否需要AI分析 (可选，值为 'true' 时启用)\n - generate_image: 是否生成图片 (可选，值为 'true' 时启用，仅支持 product_affinity)\n\n Returns:\n dict: 包含原始数据或AI分析结果的字典\n \"\"\"\n\n # 解析事件数据\n if isinstance(event, bytes):\n event = json.loads(event.decode('utf-8'))\n elif isinstance(event, str):\n event = json.loads(event)\n\n # 获取分析类型参数\n analysis_type = event.get('analysis_type')\n if not analysis_type:\n query_params = event.get('queryParameters', {})\n analysis_type = query_params.get('analysis_type')\n\n if not analysis_type:\n return {\"status\": \"error\", \"message\": \"请求中缺少 'analysis_type' 参数\"}\n\n # 获取其他可选参数\n ai_result = event.get('ai_result') or event.get('queryParameters', {}).get('ai_result')\n generate_image = event.get('generate_image') or event.get('queryParameters', {}).get('generate_image')\n\n logging.info(f\"处理请求: analysis_type={analysis_type}, ai_result={ai_result}, generate_image={generate_image}\")\n\n try:\n # 获取原始数据\n raw_data = get_raw_data(analysis_type)\n if not raw_data:\n return {\"status\": \"error\", \"message\": f\"未找到 {analysis_type} 类型的数据\"}\n\n # 构建基础返回结果\n result = {\n \"status\": \"success\",\n \"analysis_type\": analysis_type,\n \"data_count\": len(raw_data),\n \"raw_data\": raw_data\n }\n\n # 如果需要AI分析结果\n if ai_result == 'true':\n logging.info(f\"开始执行AI分析: {analysis_type}\")\n analysis_result = get_ai_analysis(analysis_type, raw_data)\n result[\"ai_analysis\"] = analysis_result\n logging.info(\"AI分析完成\")\n\n # 如果需要生成图片（目前只支持商品关联分析）\n if generate_image == 'true':\n if analysis_type == 'product_affinity':\n logging.info(\"开始生成商品组合图片\")\n image_result = ai_service.generate_product_bundle_image(raw_data)\n result[\"generated_image\"] = image_result\n logging.info(\"图片生成完成\")\n else:\n result[\"image_warning\"] = f\"图片生成功能目前只支持 'product_affinity' 类型，当前类型为 '{analysis_type}'\"\n\n return result\n\n except Exception as e:\n error_msg = f\"处理 {analysis_type} 请求时发生错误: {str(e)}\"\n logging.error(error_msg)\n return {\"status\": \"error\", \"message\": error_msg}\n\n\ndef get_raw_data(analysis_type):\n \"\"\"\n 根据分析类型获取原始数据\n\n Args:\n analysis_type (str): 分析类型\n\n Returns:\n list: 原始数据列表，如果类型不支持则返回 None\n \"\"\"\n\n if analysis_type == 'top_selling_products':\n return db.get_top_selling_products(10)\n\n elif analysis_type == 'product_affinity':\n return db.get_product_affinity_analysis(min_occurrence=5, limit=15)\n\n elif analysis_type == 'inventory_status':\n return db.get_inventory_status_analysis(30)\n\n elif analysis_type == 'customer_behavior':\n return db.get_customer_behavior_analysis(20)\n\n elif analysis_type == 'seasonal_sales':\n return db.get_seasonal_sales_trend(10)\n\n else:\n logging.warning(f\"不支持的分析类型: {analysis_type}\")\n return None\n\n\ndef get_ai_analysis(analysis_type, data):\n \"\"\"\n 根据分析类型和数据获取AI分析结果\n\n Args:\n analysis_type (str): 分析类型\n data (list): 原始数据\n\n Returns:\n str: AI分析结果文本\n \"\"\"\n\n if analysis_type == 'top_selling_products':\n return ai_service.analyze_top_selling_products(data)\n\n elif analysis_type == 'product_affinity':\n return ai_service.analyze_product_affinity(data)\n\n elif analysis_type == 'inventory_status':\n return ai_service.analyze_inventory(data)\n\n elif analysis_type == 'customer_behavior':\n return ai_service.analyze_customer_behavior(data)\n\n elif analysis_type == 'seasonal_sales':\n return ai_service.analyze_seasonal_trends(data)\n\n else:\n return f\"不支持的分析类型: {analysis_type}\""]]],["p",{"uuid":"mdg6a5s447vz3vwn75j"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]],["img",{"uuid":"mdgrbczk0msbeau56ta","name":"image.png","size":1214979,"width":748,"height":636.1236093943139,"src":"https://oss-ata.alibaba.com/article/2025/07/9a1c5ecc-f066-4a45-8df2-8bda627c2362.png","extraData":{"resourceId":"ecbe24eb-d418-4a97-9c24-17d93aab9133","metaData":{"size":1214979,"originWidth":1618,"originHeight":1376,"format":"png","ratio":1}}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["p",{"uuid":"mdg6kz6hp5lxu2wbxs"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["p",{"uuid":"mdgqz1r2bajxfj4ica","ind":{"firstLine":32}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"值得注意的是，函数计算中需要依赖的类库需要手动安装，依赖的类库如下："]]],["p",{"uuid":"mdg6a6g9zrnqwd64mam"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"requirements.txt"]]],["code",{"syntax":"python","theme":"default","wrap":true,"id":"fmeotp","title":"","fold":false,"hideHeader":false,"codeFolding":false,"showLineNumber":true,"font":{"fontFamily":"defaultFont","ligatures":false},"enableMacHeader":false,"uuid":"mdg6a23sh8qns5oxig6","code":"pymssql==2.3.7\nrequests==2.31.0\ndashscope==1.14.1\nalibabacloud-tea-openapi==0.3.7"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"pymssql==2.3.7\nrequests==2.31.0\ndashscope==1.14.1\nalibabacloud-tea-openapi==0.3.7"]]],["p",{"uuid":"mdg6jjk7fdjgdkg5gvb"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["p",{"uuid":"mdgqzpj6rmwqjg4x8g","ind":{"firstLine":32}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"在打包上传代码之前，可以使用下面命令部署依赖包："]]],["code",{"syntax":"shell","theme":"default","wrap":true,"id":"u1v2so","title":"","fold":false,"hideHeader":false,"codeFolding":false,"showLineNumber":true,"font":{"fontFamily":"defaultFont","ligatures":false},"enableMacHeader":false,"uuid":"mdg6g7l7r7hkq412bwl","code":"pip install -r requirements.txt -t ."},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"pip install -r requirements.txt -t ."]]],["p",{"uuid":"mdg66efq6z1wt9lrgqo"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["h2",{"uuid":"mdg63iuvt0ns44vynip","spacing":{"before":21.333333333333332,"after":9,"line":0.8529411764705882}},["span",{"data-type":"text"},["span",{"bold":true,"sz":16,"szUnit":"pt","data-type":"leaf"},"配置环境变量&设置端口"]]],["p",{"uuid":"mdg5v68nfvvv8p302ui","ind":{"firstLine":32}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"函数中，我们部署环境变量，存入RDS SQL Server的地址、用户名与密码，以及百炼的API Key，这里当然也可以使用KMS，但为了DEMO简单起见，使用环境变量方式"]]],["p",{"uuid":"mdg5ypja3hyahbgw2xn","ind":{"firstLine":32}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]],["img",{"uuid":"mdgrbczkemjqbval88e","name":"image.png","size":413499,"width":748,"height":309.1072847682119,"src":"https://oss-ata.alibaba.com/article/2025/07/08b5864f-35d0-45e0-acac-8789c9a9fced.png","extraData":{"resourceId":"bf46619f-22ae-47c0-bc93-42ec5a8134f5","metaData":{"size":413499,"originWidth":1510,"originHeight":624,"format":"png","ratio":1}}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["p",{"uuid":"mdg5yps8xlxolplcbsl"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]],["img",{"uuid":"mdgrbczkdwtagqcsbqf","name":"image.png","size":434155,"width":748,"height":263.2025,"src":"https://oss-ata.alibaba.com/article/2025/07/9594fdf4-c60b-4d33-85a6-c92e4c6acdb9.png","extraData":{"resourceId":"393162d5-eaf6-4fd2-bd18-c272c34aee72","metaData":{"size":434155,"originWidth":1808,"originHeight":636,"format":"png","ratio":1}}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["p",{"uuid":"mdgr1dq7dpn6ksq7yvr"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["p",{"uuid":"mdgr1dzskyytqz4cfkn","ind":{"firstLine":32}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"通过创建触发器，允许函数通过HTTP请求触发，如下图："]]],["p",{"uuid":"mdg5zooe824p33sfs0u"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]],["img",{"uuid":"mdgrbczkngcqjtdl8m","name":"image.png","size":470435,"width":748,"height":281.90250000000003,"src":"https://oss-ata.alibaba.com/article/2025/07/b04afce6-c7f4-4715-b280-228df911f1f9.png","extraData":{"resourceId":"1c564004-2fb2-45ff-b6d4-2baa6d19cd79","metaData":{"size":470435,"originWidth":1656,"originHeight":624,"format":"png","ratio":1}}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["p",{"uuid":"mdgr1q59v5uh8wuqx1i"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["p",{"uuid":"mdgr272fnxl2ey4fze8","ind":{"firstLine":32}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"为了DEMO简单，选择无需认证，但在生产环境中不能这么做。"]]],["p",{"uuid":"mdg60kehp1434jf6po8"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]],["img",{"uuid":"mdgrbczkwwutz2ugbfd","name":"image.png","size":866508,"width":558,"height":757,"src":"https://oss-ata.alibaba.com/article/2025/07/65c6315d-0a43-448f-a887-f026468b834e.png","extraData":{"resourceId":"62659c0a-a398-488e-a35c-b89bdca85901","metaData":{"size":866508,"originWidth":1116,"originHeight":1514,"format":"png","ratio":1}}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["p",{"uuid":"mdg5v6h3kl4px0kvmpb"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]],["img",{"uuid":"mdgrbczkl6td77az1zf","name":"image.png","size":745950,"width":748,"height":282.8375,"src":"https://oss-ata.alibaba.com/article/2025/07/f9fb42f8-edfd-4053-beff-66d30e2f25b6.png","extraData":{"resourceId":"97689bd8-900c-4269-ae25-97357b3162a6","metaData":{"size":745950,"originWidth":2146,"originHeight":812,"format":"png","ratio":1}}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["p",{"uuid":"mdg62yp74de9uoruqha"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["h2",{"uuid":"mdg641pwyexjvceijv","ind":{},"spacing":{"before":21.333333333333332,"after":9,"line":0.8529411764705882}},["span",{"data-type":"text"},["span",{"bold":true,"sz":16,"szUnit":"pt","data-type":"leaf"},"验证函数"]]],["p",{"uuid":"mdg0ujjyav26p65i8v5","ind":{"firstLine":32}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"函数创建完成后，调用函数查看结果，看起来一切正常："]]],["p",{"uuid":"mdg0uriiijyiopoy7nr","ind":{}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]],["img",{"uuid":"mdgrbczkb3x2t8f7sjj","name":"image.png","size":1903503,"width":748,"height":480.1225,"src":"https://oss-ata.alibaba.com/article/2025/07/0a2e200c-5376-493d-be86-da4f9e8ab569.png","extraData":{"resourceId":"d3ba648e-71ce-461e-8da1-0b25bd59eb6a","metaData":{"size":1903503,"originWidth":2174,"originHeight":1396,"format":"png","ratio":1}}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["p",{"uuid":"mdg0urslfha97klt1a"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["p",{"uuid":"mdg0kj3idiuogtnnr1v"},["span",{"data-type":"text"},["span",{"data-type":"leaf"},""]]],["h1",{"uuid":"mdg01o68v6dcxnw0iw","spacing":{"before":26.666666666666664,"after":9,"line":0.8529411764705882}},["span",{"data-type":"text"},["span",{"bold":true,"sz":20,"szUnit":"pt","data-type":"leaf"},"前端"]]],["p",{"uuid":"mdgr3fapa71em2zt0s","ind":{"firstLine":32}},["span",{"data-type":"text"},["span",{"data-type":"leaf"},"前端使用Flask + 静态页面的方式进行。较为简单，核心代码如下："]]],["code",{"syntax":"html","theme":"default","wrap":true,"id":"ilqvxn","title":"","fold":false,"hideHeader":false,"codeFolding":false,"showLineNumber":true,"font":{"fontFamily":"defaultFont","ligatures":false},"enableMacHeader":false,"uuid":"mdgr4norsl7gk1l742","code":"from flask import Flask, render_template, jsonify, request\nimport requests\nimport logging\n\nimport os\n\n# Initialize Flask app with explicit template folder\napp = Flask(__name__, template_folder=os.path.join(os.path.dirname(os.path.abspath(__file__)), 'templates'))\napp.secret_key = 'your-secret-key-here'\n\n# 配置日志\nlogging.basicConfig(level=logging.INFO)\nlogger = logging.getLogger(__name__)\n\n# API基础URL\nAPI_BASE_URL = \"https://sales-insights-zfdsymcpgw.cn-beijing.fcapp.run\"\n\n# 分析类型配置\nANALYSIS_TYPES = {\n 'top_selling_products': {\n 'name': '热销商品分析',\n 'description': '展示销售额最高的商品及其详细信息，包括收入、客户数量、平均价格等',\n 'icon': '来源：程序园用户自行投稿发布，如果侵权，请联系站长删除
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！
页:
[1]