一、前言
作为地区销售部门，有很多指标需要跟踪。从收入或订单数量等高级指标，到客户反馈等更详细的信息，作为部门的领导，可能需要花费大量的时间从数据堆中找出每次需要的不同数据。客户的销售基本上都是通过线上或者手机端完成的，统一存储在Mysql数据库中。
作为数据可视化开发，客户的基本面已经完成了在数据收集、数据清洗和数据过滤。接下来的主要工作是数据筛选和统计、数据格式化输入即可。
二、项目介绍
项目是某奶品在华东区线下销售的数据统计平台，主要呈现每日奶品销售的信息数据。如销售总金额、区域销售排行、产品品类销售排行、24小时销售量和销售额、不同网点的销售趋势等等。
从需求上可以分析得出，大屏一是要满足数据展示类，二是要满足决策者实时操作比对不同时间节点的销售数据，和后端数据进行交互，三是实时数据，顾名思义指的就是数据在实时传输，产生了数据就要立即展示出来。
三、项目展示
四、项目经验分享
电商数据展示，主要的数据指标，昨日、今日、7日、30日、本月、上月等多维度环比的展示，这对mysql语句的熟练掌握和实际应用有很大的挑战。也正是该项目的顺利完成，让自己对mysql有了更深度的认识。
4.1 翻牌器
4.1.1 翻牌器-今日实时交易
作为当日交易总金额，是整个大屏最为关注的点睛之处。因此，采用了numberAnimate翻牌器效果，实时刷新数据，一目了解掌握大局。
4.1.2.翻牌器后端统计SUM函数的使用
使用数据表中的当天记录和mysql内置函数CURDATE()，进行条件筛选判断，date_format(a.paid_at,'%Y-%m-%d') = CURDATE()
public function getToday() { $sql = "select SUM(a.should_paid) AS totalPays,a.uniacid,b.uniacid,b.dpopen from " . $db->table('orders') . " AS a, " . $db->table('set_basic') . " AS b where a.status <> '-128'";// AND a.status <> '-126' $sql .= " AND date_format(a.paid_at,'%Y-%m-%d') = CURDATE() AND a.uniacid = b.uniacid"; $row = $db->queryall($sql); $res["data"] = $row; die(json_encode_lockdata($res)); }
4.2 不同时间指标的数据
MySql内部的时间计算
不同的时间指标对应不同的计算方式，充分利用mysql内置函数进行计算。通过数据可视化大屏前端点击切换时间指标来动态获取具体的时间数据指标。
昨天 ：TO_DAYS(CURDATE()) - TO_DAYS(date_format(a.paid_at,'%Y-%m-%d')) = 1
今天 ： date_format(a.paid_at,'%Y-%m-%d') = CURDATE()；
近7天：DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= date(a.paid_at)
本月：date_format(a.paid_at,'%Y-%m') = date_format(CURDATE(),'%Y-%m')
上月：date_format(a.paid_at,'%Y-%m') = date_format(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m')
近30天： DATE_SUB(CURDATE(), INTERVAL 29 DAY) <= date(a.paid_at)
public function getOrders() { global $db, $res; dbc(); /*间隔时间天数,-1昨天，0-当日;7-近7日；30-近30天; * 过滤$days,如果无参数，默认为当日 * */ @$CR_days = $_POST['days']; if ($CR_days == "") { $CR_days = "0"; } $sql = "select COUNT(a.id) AS transTotal,SUM(a.should_paid) AS totalPays,a.paid_at,a.uniacid,b.uniacid,b.dpopen from " . $db->table('orders') . " AS a, " . $db->table('set_basic') . " AS b where a.status <> '-128' AND a.uniacid = b.uniacid";//AND a.status <> '-126' /*判断日期间隔*/ switch ($CR_days) { case -1; $sql .= " AND TO_DAYS(CURDATE()) - TO_DAYS(date_format(a.paid_at,'%Y-%m-%d')) = 1"; break; case 0; $sql .= " AND date_format(a.paid_at,'%Y-%m-%d') = CURDATE()"; break; case 7; $sql .= " AND DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= date(a.paid_at)"; break; case 2;//本月，修改 BY jieji007,2020-08-31 $sql .= " AND date_format(a.paid_at,'%Y-%m') = date_format(CURDATE(),'%Y-%m')"; break; case 3;//上月，修改 BY jieji007,2020-08-31 $sql .= " AND date_format(a.paid_at,'%Y-%m') = date_format(DATE_SUB(CURDATE(), INTERVAL 1 MONTH),'%Y-%m')"; break; case 30; $sql .= " AND DATE_SUB(CURDATE(), INTERVAL 29 DAY) <= date(a.paid_at)"; break; default: $sql .= " AND date_format(a.paid_at,'%Y-%m-%d') = CURDATE()"; } $row = $db->queryall($sql); $res["data"] = $row; die(json_encode_lockdata($res)); }
4.3 实时交易播报
实时交易播报，开发难点在于需要多次跨表查询。
- 获取当日交易的站点及名称；
- 获取对应站点团长的数量；
- 获取对应站点团长的数量；
- 获取对应的站点会员数量；
联表查询后，再次循环不同的数据表，方便生成json数据，传送至前端。
MySql联表查询和内部遍历循环
public function getTransMaster() { global $db, $res; dbc(); //获取当日交易的站点及名称; $sql = "select a.uniacid,SUM(a.should_paid) AS totalPays,COUNT(a.id) AS transTotal,MAX(a.paid_at) AS paid_at,b.uniacid,b.name,b.dpopen from " . $db->table('orders'); $sql .= " AS a, " . $db->table('set_basic') . " AS b"; $sql .= " WHERE date_format(a.paid_at,'%Y-%m-%d') = CURDATE() AND a.uniacid = b.uniacid AND b.dpopen = 1"; $sql .= " GROUP BY a.uniacid ORDER BY totalPays DESC LIMIT 15"; $row = $db->queryall($sql); //获取对应站点团长的数量; foreach ($row as $k => $v) { $sql_l = "select uniacid,IFNULL(COUNT(id),0) AS leaderTotal,server,legitimate,created_at from " . $db->table('leader'); $sql_l .= " WHERE date_format(created_at,'%Y-%m-%d') = CURDATE() AND uniacid =" . $v['uniacid'];//server = 1 AND legitimate = 1 AND $sql_l .= " GROUP BY uniacid"; $row[$k]["leaderTotal"] = $db->queryall($sql_l); } //获取对应的站点会员数量; foreach ($row as $k => $v) { $sql_m = "select COUNT(id) AS memberTotal,created_at,uniacid,legitimate from " . $db->table('member'); $sql_m .= " WHERE legitimate = 1 AND date_format(created_at,'%Y-%m-%d') = CURDATE() AND uniacid =" . $v['uniacid']; $sql_m .= " GROUP BY uniacid"; $row[$k]["memberTotal"] = $db->queryall($sql_m); } $res["data"] = $row; die(json_encode_lockdata($res)); }
4.4 每日交易量
准确的表述，应该是近30日的交易量/交易额柱图。方便决策者迅速有效的发现每日销售数据的差异，快速做出反应，进行销售问题的反馈和解决。
4.4.1.近30日交易量/交易额API
为防止日统计无效，使用到了ifnull(COUNT(a.id),0) AS totalTrans函数；
日期格式化DATE_FORMAT(a.paid_at,'%Y-%m-%d')，便于Echarts的图表的展示；
//近30日交易量; public function getEveryDay() { global $db, $res; dbc(); $sql = "select DATE_FORMAT(a.paid_at,'%Y-%m-%d') as dataKey,ifnull(COUNT(a.id),0) AS totalTrans,ifnull(SUM(a.should_paid),0) AS totalPays,b.uniacid,b.dpopen from " . $db->table('orders') . " AS a, " . $db->table('set_basic') . " AS b WHERE a.status <> '-128' AND a.uniacid = b.uniacid";// AND a.status <> '-126' if (@$_COOKIE['Controller'] == "-1") { $sql .= " AND a.uniacid = " . $_COOKIE['uniacid']; } else { $sql .= " AND b.dpopen = 1"; } $sql .= " GROUP BY DATE_FORMAT(a.paid_at,'%Y-%m-%d') ORDER BY a.paid_at DESC LIMIT 30"; $row = $db->queryall($sql); $res["data"] = $row; die(json_encode_lockdata($res)); }
4.4.1.近30日交易量/交易额Echarts
//柱状图 function getBar(dataName, dataList1, dataList2) { var myChart = echarts.init(document.getElementById("trans")); var option = { tooltip: { trigger: 'axis', axisPointer: { lineStyle: { color: '#57617B' } } }, dataZoom: [{ show: false, start: 0, end: 50, type: "inside", }], legend: { data: [ {"name": "交易量"}, {"name": "交易额"} ], top: '2%', icon: 'circle', textStyle: { "color": "rgba(255,255,255,0.9)" } }, xAxis: [ { "type": "category", data: dataName, axisLine: {lineStyle: {color: "rgba(255,255,255,.1)"}}, inverse: true, axisLabel: { textStyle: {color: "rgba(255,255,255,.6)", fontSize: '12'} } } ], yAxis: [ { type: "value", axisLabel: {show: true}, axisLine: {lineStyle: {color: 'rgba(255,255,255,.4)'}}, splitLine: {show: true, lineStyle: {color: "rgba(255,255,255,.2)"}} }, { type: "value", show: true, axisLabel: {show: true}, axisLine: {lineStyle: {color: 'rgba(255,255,255,.4)'}}, splitLine: {show: true, lineStyle: {color: "rgba(255,255,255,0)"}} } ], grid: { top: '13%', right: '2%', left: '2%', bottom: '5%', containLabel: true }, series: [ { name: "交易量", type: "bar", data: dataList1, barWidth: '50%', yAxisIndex: 1, itemStyle: { normal: { color: new echarts.graphic.LinearGradient(0, 0, 0, 1, [{ offset: 0, color: '#39e7d7' }, { offset: 1, color: '#10A7DB' }], false), barBorderRadius: [2, 2, 0, 0], shadowColor: 'rgba(0,255,225,1)', shadowBlur: 4 } } }, { name: "交易额", type: 'line', smooth: true, symbol: 'circle', symbolSize: 5, //showSymbol: false, yAxisIndex: 0, data: dataList2, lineStyle: { normal: { width: 2 }, }, itemStyle: { color: '#cdba00', borderColor: 'rgba(0,136,212,0.2)', borderWidth: 12 } } ] }; myChart.setOption(option); window.addEventListener("resize", function () { myChart.resize(); }); }
4.5 24小时销售额/销售量折线图
4.5.1.GROUP BY 条件
- 筛选条件
TO_DAYS(CURDATE()) - TO_DAYS(date_format(a.paid_at,'%Y-%m-%d')) = 1 - 分组统计规则
GROUP BY DATE_FORMAT(a.paid_at,'%Y-%m-%d %H')
public function getTwentyFour() { global $db, $res; dbc(); //最近24小时记录; $sql = "select RIGHT(DATE_FORMAT(a.paid_at,'%Y-%m-%d %H'),2) as dataKey,ifnull(COUNT(a.id),0) AS totalTrans,ifnull(SUM(a.should_paid),0) AS totalPays,b.uniacid,b.dpopen from " . $db->table('orders') . " AS a, " . $db->table('set_basic') . " AS b WHERE a.paid_at <> '' AND DATE_FORMAT(a.paid_at,'%Y-%m-%d') = CURDATE()"; $sql .= " AND a.status <> '-128' AND a.uniacid = b.uniacid AND b.dpopen = 1";//AND a.status <> '-126' $sql .= " GROUP BY DATE_FORMAT(a.paid_at,'%Y-%m-%d %H')"; $row = $db->queryall($sql); $res["data"] = $row; //上24小时记录; $sql_p = "select RIGHT(DATE_FORMAT(a.paid_at,'%Y-%m-%d %H'),2) as dataKey,ifnull(COUNT(a.id),0) AS totalTrans,ifnull(SUM(a.should_paid),0) AS totalPays,b.uniacid,b.dpopen from " . $db->table('orders') . " AS a, " . $db->table('set_basic') . " AS b WHERE a.paid_at <> '' AND TO_DAYS(CURDATE()) - TO_DAYS(date_format(a.paid_at,'%Y-%m-%d')) = 1"; $sql_p .= " GROUP BY DATE_FORMAT(a.paid_at,'%Y-%m-%d %H')"; $row_p = $db->queryall($sql_p); $res["pdata"] = $row_p; die(json_encode_lockdata($res)); }
4.5.2.近24小时/上24小时
//折线图 function getLine(dataName, dataList1, dataList2) { var myChart = echarts.init(document.getElementById('twentyFour')); var option = { tooltip: { trigger: 'axis', axisPointer: { lineStyle: { color: '#57617B' } } }, legend: { data: ['近24小时', '上24小时'], y: 'top', textStyle: { color: "#fff" }, itemGap: 20, }, grid: { top: '15%', right: '2%', left: '2%', bottom: '5%', containLabel: true }, xAxis: [{ type: 'category', boundaryGap: false, interval: 0, axisLabel: { show: true, textStyle: { color: 'rgba(255,255,255,.6)' } }, axisLine: { lineStyle: { color: 'rgba(255,255,255,.1)' } }, data: dataName }], yAxis: [{ axisLabel: { show: true, textStyle: { color: 'rgba(255,255,255,.6)' } }, axisLine: { lineStyle: { color: 'rgba(255,255,255,.1)' } }, splitLine: { lineStyle: { color: 'rgba(255,255,255,.1)' } } }], series: [{ name: '近24小时', type: 'line', connectNulls: true, smooth: true, symbol: 'circle', symbolSize: 3, showSymbol: true, lineStyle: { normal: { width: 2 } }, areaStyle: { normal: { color: new echarts.graphic.LinearGradient(0, 0, 0, 1, [{ offset: 0, color: 'rgba(24, 163, 64, 0.3)' }, { offset: 0.8, color: 'rgba(24, 163, 64, 0)' }], false), shadowColor: 'rgba(0, 0, 0, 0.1)', shadowBlur: 10 } }, itemStyle: { normal: { color: '#cdba00', borderColor: 'rgba(137,189,2,0.27)', borderWidth: 12 } }, data: dataList1 }, { name: '上24小时', type: 'line', connectNulls: true, smooth: true, symbol: 'circle', symbolSize: 3, showSymbol: true, lineStyle: { normal: { width: 2 } }, areaStyle: { normal: { color: new echarts.graphic.LinearGradient(0, 0, 0, 1, [{ offset: 0, color: 'rgba(39, 122,206, 0.3)' }, { offset: 0.8, color: 'rgba(39, 122,206, 0)' }], false), shadowColor: 'rgba(0, 0, 0, 0.1)', shadowBlur: 10 } }, itemStyle: { normal: { color: '#277ace', borderColor: 'rgba(0,136,212,0.2)', borderWidth: 12 } }, data: dataList2 }] }; myChart.setOption(option); window.addEventListener("resize", function () { myChart.resize(); }); }
4.6 筛选时间切换封装函数
4.6.1.获取当天日期
function getDate(days) { //定义今日标准时间; var d = new Date(); var nowYear = d.getFullYear().toString();//今年 var nowMonth = timeAdd0((d.getMonth() + 1).toString());//当月 var nowDay = timeAdd0(d.getDate().toString());//当日 var now = nowYear + "-" + nowMonth + "-" + nowDay;//今日标准时间; 2020-09-02 //日期计算; var startDate = new Date(Date.parse(now.replace(/-/g, '/'))); //将开始时间由字符串格式转换为日期格式 var value = startDate.getTime(); //将开始时间转为毫秒 value += days * (24 * 3600 * 1000); //将天数转换成毫秒后与开始时间相加得到结束时间的毫秒数 var d2 = new Date(value); //将得到的毫秒数转换为日期 var nowYear2 = d2.getFullYear().toString();//今年 var nowMonth2 = timeAdd0((d2.getMonth() + 1).toString());//当月 var nowDay2 = timeAdd0(d2.getDate().toString());//当日 var endDate = nowYear2 + "-" + nowMonth2 + "-" + nowDay2; return endDate; }
4.6.2.获取当月日期
//获取当月; function getMonths(mons, today) { var d = new Date(); var nowYear = d.getFullYear().toString();//今年 var nowMonth = timeAdd0((d.getMonth() + 1 + Number(mons)).toString());//当月 var nowDay = timeAdd0(d.getDate().toString());//当日 if (today == "0") { var now = nowYear + "-" + nowMonth + "-" + nowDay;//今日标准时间; 2020-09-02 } else { var now = nowYear + "-" + nowMonth + "-" + "01";//今日标准时间; 2020-09-02 } return now; }
4.6.3.获取上月日期
//获取上月; function getLastMonths(mons, today) { var d = new Date(); var nowYear = d.getFullYear().toString();//今年 var nowMonth = timeAdd0((d.getMonth() + 1 + Number(mons)).toString());//当月 if (today == "1") { var now = nowYear + "-" + nowMonth + "-" + "01";//今日标准时间; 2020-09-02 } if (today == "30") { var now = nowYear + "-" + nowMonth + "-" + mGetDate(Number(mons));//今日标准时间; 2020-09-02 } return now; }
4.6.3.获取当前月份天数
//获取当前月份天数: function mGetDate(mons) { var date = new Date(); var year = date.getFullYear(); var month = date.getMonth() + 1 + Number(mons); var d = new Date(year, month, 0); return d.getDate(); }
总结
该项目是初期入门数据可视化大屏的作品，严格意义上，是在懵懂的状态下完成的开发。随时开发作品的不断增加，对项目销售的了解、开发过程的深入、大数据量的学习，回头再看，总有些可以精进的地方。
@漏刻有时
