公司与产品时序图数据建模

Here’s the table of contents:

1. 整体数据模型的设计
2. 函数与过程功能介绍
3. 完整实现 - 构架公司与产品时序图数据
4. 查询案例-分析’消费品商贸’产业2020Q2季度总营收

整体数据模型的设计

行业、产品、公司相关的产业链数据建模全景图，包含产品层级图谱、行业分层图谱、产业链上下游图谱、公司与产品图谱等。如下主要是公司与产品营收的时序图数据建模实现方案。

函数与过程功能介绍

• HTTP请求

  RETURN olab.http.post('api-address','input')
  

• 重置MAP-移除传入的KEY

  RETURN olab.reset.map({map},{keys}) AS value
  

• 生成JSON-STRING

  RETURN olab.convert.json({object})
  

完整实现 - 构架公司与产品时序图数据

• 在下面的实现中营收相关的时序使用JSON格式数据建模存储在关系的属性中

  CALL apoc.periodic.iterate('CALL apoc.load.jdbc(\'jdbc:mysql://alibaba.com.cn:3306/database?user=ur&password=pwd&useUnicode=true&characterEncoding=utf8&serverTimezone=UTC\', \'SELECT calc.company_id AS fromCompanyId,pro.`name` AS `to`,CONVERT(DATE_FORMAT(calc.report_date,\\\'%Y%m%d%H%i%S\\\'),UNSIGNED INTEGER) AS report_date,income,profit,product_income,product_income_ratio,product_profit,product_profit_ratio,\\\'id\\\' AS uniqueField,calc.id AS uniqueFieldID,\\\'chinascope_supply_chain.fin_secu_sam_product_calc\\\' AS jsDataApi FROM fin_secu_sam_product_calc calc RIGHT JOIN dict_product_rs pro ON calc.product_code=pro.`code` WHERE calc.update_time>=DATE_SUB(NOW(),INTERVAL 1 DAY)\')','WITH REPLACE(\'{"query": "{  horgByName(sourceCode: \\\\\\"company-id\\\\\\", sourceFlag: \\\\\\"chinascope\\\\\\") {    name    hcode  }}","variables": null}\',\'company-id\',row.fromCompanyId) AS query,row WITH apoc.convert.fromJsonMap(olab.http.post(\'http://10.20.13.130/ongdb/graphql\',query)) AS result,row WHERE result IS NOT NULL WITH result.data.horgByName[0].hcode AS fromHcode,result.data.horgByName[0].name AS fromName,row WHERE fromHcode IS NOT NULL WITH fromHcode,fromName,row.to AS toName,row WITH fromHcode,fromName,\'HPRDCLS\'+apoc.util.md5([toName]) AS toHcode,row MERGE (from:HORGProductCalc {hcode:fromHcode}) SET from.name=fromName,from:HORG WITH from,toHcode,row MATCH (to:产业 {hcode:toHcode}) WITH from,to,row WHERE from<>to WITH from,to,olab.reset.map(row,[\'fromCompanyId\',\'to\']) AS row OPTIONAL MATCH (from)-[r]->(to) WITH from,to,row,apoc.coll.union(apoc.convert.fromJsonList(r.detail),[row]) AS detail WITH from,to,row,olab.convert.json(detail) AS detail,SIZE(detail) AS detail_size MERGE (from)-[r:产品]->(to) SET r.detail=detail,r.detail_size=detail_size', {parallel:false,batchSize:100}) YIELD batches,total,timeTaken,committedOperations,failedOperations,failedBatches,retries,errorMessages,batch,operations RETURN batches,total,timeTaken,committedOperations,failedOperations,failedBatches,retries,errorMessages,batch,operations;
  

  

查询案例-分析’消费品商贸’产业2020Q2季度总营收

• 查询’消费品商贸’行业下级分类中所有相关产品

  MATCH (indus:行业) WHERE indus.name='消费品商贸' WITH indus
  CALL apoc.path.expandConfig(indus,{maxLevel:3, relationshipFilter:'NEXT>|<BELONG_TO', labelFilter:'+产业|/产品', bfs:false, filterStartNode:true,uniqueness:'NODE_GOLBEL', limit:-1}) YIELD path WITH path
  RETURN path
  

  MATCH (indus:行业) WHERE indus.name='消费品商贸' WITH indus
  CALL apoc.path.subgraphNodes(indus,{maxLevel:3, relationshipFilter:'NEXT>|<BELONG_TO', labelFilter:'+产业|/产品', bfs:false, filterStartNode:true,uniqueness:'NODE_GOLBEL', limit:-1}) YIELD node WITH node
  RETURN node
  

  

• ‘消费品商贸’行业相关的产品2020Q2营收分析【营收加总】

  MATCH (indus:行业) WHERE indus.name='消费品商贸' WITH indus
  CALL apoc.path.subgraphNodes(indus,{maxLevel:3, relationshipFilter:'NEXT>|<BELONG_TO', labelFilter:'+产业|/产品', bfs:false, filterStartNode:true,uniqueness:'NODE_GOLBEL', limit:-1}) YIELD node WITH node
  MATCH p=(node)<-[r:产品]-(:HORGProductCalc) WITH apoc.convert.fromJsonList(r.detail) AS detail
  UNWIND detail AS map
  WITH map WHERE TOINTEGER(map.report_date)>=20200331000000 AND TOINTEGER(map.report_date)<=20200630000000
  WITH TOINTEGER(map.income) AS income
  RETURN SUM(income)
  # 加总后的总营收两万亿
  ╒═════════════╕
  │"SUM(income)"│
  ╞═════════════╡
  │2215100453627│
  └─────────────┘