Mule中DataWeave日期解析及filter/map/groupBy开发问题排查

基础数据源 #

本次开发使用的CSV销售数据结构如下：

Region,Country,ItemType,SalesChannel,OrderPriority,OrderDate,OrderID,ShipDate,UnitsSold,UnitPrice,UnitCost,TotalRevenue,TotalCost,TotalProfit
Central America and the Caribbean,Antigua and Barbuda ,Baby Food,Online,M,12/20/2013,957081544,1/11/2014,552,255.28,159.42,140914.56,87999.84,52914.72
Central America and the Caribbean,Panama,Snacks,Offline,C,7/5/2010,301644504,7/26/2010,2167,152.58,97.44,330640.86,211152.48,119488.38
Europe,Czech Republic,Beverages,Offline,C,9/12/2011,478051030,9/29/2011,4778,47.45,31.79,226716.10,151892.62,74823.48
Asia,North Korea,Cereal,Offline,L,5/13/2010,892599952,6/15/2010,9016,205.70,117.11,1854591.20,1055863.76,798727.44
Asia,Sri Lanka,Snacks,Offline,C,7/20/2015,571902596,7/27/2015,7542,152.58,97.44,1150758.36,734892.48,415865.88
Middle East and North Africa,Morocco,Personal Care,Offline,L,11/8/2010,412882792,11/22/2010,48,81.73,56.67,3923.04,2720.16,1202.88
Australia and Oceania,Federated States of Micronesia,Clothes,Offline,H,3/28/2011,932776868,5/10/2011,8258,109.28,35.84,902434.24,295966.72,606467.52
Europe,Bosnia and Herzegovina,Clothes,Online,M,10/14/2013,919133651,11/4/2013,927,109.28,35.84,101302.56,33223.68,68078.88
Middle East and North Africa,Afghanistan,Clothes,Offline,M,8/27/2016,579814469,10/5/2016,8841,109.28,35.84,966144.48,316861.44,649283.04
Sub-Saharan Africa,Ethiopia,Baby Food,Online,M,4/13/2015,192993152,5/7/2015,9817,255.28,159.42,2506083.76,1565026.14,941057.62
Middle East and North Africa,Turkey,Office Supplies,Offline,C,9/25/2013,557156026,10/15/2013,3704,651.21,524.96,2412081.84,1944451.84,467630.00
Middle East and North Africa,Oman,Cosmetics,Online,M,5/12/2013,741101920,5/17/2013,7382,437.20,263.33,3227410.40,1943902.06,1283508.34
Asia,Malaysia,Cereal,Offline,L,7/31/2016,333942162,8/25/2016,9762,205.70,117.11,2008043.40,1143227.82,864815.58
Central America and the Caribbean,Saint Lucia,Cosmetics,Offline,H,7/6/2015,795100581,7/16/2015,6786,437.20,263.33,2966839.20,1786957.38,1179881.82
Central America and the Caribbean,Saint Vincent and the Grenadines,Baby Food,Online,L,11/28/2010,504313504,12/3/2010,6428,255.28,159.42,1640939.84,1024751.76,616188.08
Middle East and North Africa,Lebanon,Meat,Offline,H,12/17/2015,611629760,1/31/2016,3693,421.89,364.69,1558039.77,1346800.17,211239.60
Europe,Austria,Cereal,Offline,C,8/13/2014,987410676,9/6/2014,5616,205.70,117.11,1155211.20,657689.76,497521.44
Europe,Bulgaria,Office Supplies,Online,L,10/31/2010,672330081,11/29/2010,6266,651.21,524.96,4080481.86,3289399.36,791082.50
North America,Mexico,Beverages,Online,C,3/13/2017,127374303,3/20/2017,1742,47.45,31.79,82657.90,55378.18,27279.72

接口a：指定日期订单列表 #

原代码错误点 #

• 字段名大小写不匹配：CSV解析后字段名保留原始表头的驼峰大写格式OrderDate，原代码写为$.orderDate，读取不到对应字段导致转换空值报错
• 日期格式不匹配：CSV内日期为M/d/yyyy格式（月、日无前导零），直接使用as Date默认会按ISO标准yyyy-MM-dd格式解析，必然转换失败
• 缺少可选参数Country的过滤逻辑，仅实现了日期过滤
• 传入的查询参数orderdate为字符串类型，未做同格式日期转换，和解析后的Date对象类型不匹配，比对永远不成立

正确实现代码 #

%dw 2.0
output application/json
// 定义CSV使用的日期格式
var dateFormat = "M/d/yyyy"
// 把传入的订单日期参数转为Date类型
var queryOrderDate = attributes.queryparam.orderdate as Date {format: dateFormat}
// 读取可选的国家参数
var queryCountry = attributes.queryparam.country default null
---
payload 
// 先过滤符合日期条件的数据
filter (($.OrderDate as Date {format: dateFormat}) == queryOrderDate)
// 再判断如果传了国家参数，追加国家过滤条件
filter (queryCountry == null or trim($.Country) == trim(queryCountry))
// 映射返回所有要求的字段
map ((item) -> {
    region: item.Region,
    country: trim(item.Country),
    itemType: item.ItemType,
    salesChannel: item.SalesChannel,
    orderPriority: item.OrderPriority,
    orderDate: item.OrderDate,
    orderID: item.OrderID,
    shipDate: item.ShipDate,
    unitsSold: item.UnitsSold as Number,
    unitPrice: item.UnitPrice as Number,
    unitCost: item.UnitCost as Number,
    totalRevenue: item.TotalRevenue as Number,
    totalCost: item.TotalCost as Number,
    totalProfit: item.TotalProfit as Number
})

注：代码中对Country字段做了trim处理，避免原始CSV中部分国家名前后带空格导致匹配失败，同时把数值类字段转成Number类型，避免前端拿到字符串影响后续计算

接口b：销售报表接口 #

原代码错误点 #

• 执行顺序错误：原代码先执行map再执行groupBy，groupBy作用在单条映射后的对象上，无法对整个数据集做分组
• 分组维度错误：需求要求按Country+OrderPriority双维度分组，原代码仅按Country单字段分组
• 聚合逻辑错误：总订单数硬编码为100，总收入未做分组后的求和计算，仅取单条记录的TotalRevenue值
• 类型错误：TotalRevenue从CSV读取后为字符串类型，未转数值直接聚合会出现字符串拼接的异常结果

正确实现代码 #

%dw 2.0
output application/json
var queryChannel = attributes.queryparam.salesChannel
---
payload
// 先过滤匹配销售渠道的数据
filter ($.SalesChannel == queryChannel)
// 按国家+订单优先级双维度分组
groupBy ((item) -> (item.Country ++ "_" ++ item.OrderPriority))
// 对每个分组做聚合计算
pluck ((groupItems) -> {
    country: trim(groupItems[0].Country),
    orderPriority: groupItems[0].OrderPriority,
    totalNumberofOrders: sizeOf(groupItems),
    totalRevenue: sum(groupItems map ($.TotalRevenue as Number))
})

注：双维度分组时用两个字段拼接作为分组key，避免不同国家同优先级、同国家不同优先级的数据混在一起；聚合完成后用pluck把分组后的对象转成数组结构，更符合接口返回的列表格式

内容的提问来源于stack exchange，提问作者Aravind