XQuery应用实例:对双色球的统计及分析(初级版)

早些时候用java写过一个自动更新采集双色球开奖记录并保存相关信息到MySQL的小工具.

MySQL支持将表中的内容导出为XML格式.

正好最近在学习XQuery,所以萌生了想用XQuery来统计/分析对应记录的想法.

主要是的思路是通过设置期号区间/红球/蓝球等多种条件组合的方式来统计/分析.

解析器 Saxon - Java

命令: java net.sf.saxon.Query LotteryAnalyze.xq

开奖记录数据源: LotteryResults.xml(由于记录超过2000多条,仅截取2016年部分)

<?xml version="1.0" encoding="GB2312" standalone="yes"?><data><row><issue>2016001</issue><redBalls>061316182022</redBalls><blueBall>13</blueBall><date>2016-01-03（日）</date></row><row><issue>2016002</issue><redBalls>091417202430</redBalls><blueBall>16</blueBall><date>2016-01-05（二）</date></row><row><issue>2016003</issue><redBalls>011014232628</redBalls><blueBall>01</blueBall><date>2016-01-07（四）</date></row><row><issue>2016004</issue><redBalls>081017222533</redBalls><blueBall>12</blueBall><date>2016-01-10（日）</date></row><row><issue>2016005</issue><redBalls>111418203133</redBalls><blueBall>14</blueBall><date>2016-01-12（二）</date></row><row><issue>2016006</issue><redBalls>131618202831</redBalls><blueBall>12</blueBall><date>2016-01-14（四）</date></row><row><issue>2016007</issue><redBalls>051214202729</redBalls><blueBall>06</blueBall><date>2016-01-17（日）</date></row><row><issue>2016008</issue><redBalls>021524293233</redBalls><blueBall>02</blueBall><date>2016-01-19（二）</date></row><row><issue>2016009</issue><redBalls>101424252732</redBalls><blueBall>04</blueBall><date>2016-01-21（四）</date></row><row><issue>2016010</issue><redBalls>020412141925</redBalls><blueBall>06</blueBall><date>2016-01-24（日）</date></row><row><issue>2016011</issue><redBalls>030810152229</redBalls><blueBall>12</blueBall><date>2016-01-26（二）</date></row><row><issue>2016012</issue><redBalls>071214162732</redBalls><blueBall>15</blueBall><date>2016-01-28（四）</date></row><row><issue>2016013</issue><redBalls>071221222631</redBalls><blueBall>01</blueBall><date>2016-01-31（日）</date></row><row><issue>2016014</issue><redBalls>020810182027</redBalls><blueBall>07</blueBall><date>2016-02-02（二）</date></row><row><issue>2016015</issue><redBalls>010214222526</redBalls><blueBall>07</blueBall><date>2016-02-04（四）</date></row><row><issue>2016016</issue><redBalls>012022242526</redBalls><blueBall>16</blueBall><date>2016-02-14（日）</date></row><row><issue>2016017</issue><redBalls>050608202230</redBalls><blueBall>05</blueBall><date>2016-02-16（二）</date></row><row><issue>2016018</issue><redBalls>121314172125</redBalls><blueBall>04</blueBall><date>2016-02-18（四）</date></row><row><issue>2016019</issue><redBalls>061316172330</redBalls><blueBall>10</blueBall><date>2016-02-21（日）</date></row><row><issue>2016020</issue><redBalls>010210122224</redBalls><blueBall>10</blueBall><date>2016-02-23（二）</date></row><row><issue>2016021</issue><redBalls>091113222426</redBalls><blueBall>05</blueBall><date>2016-02-25（四）</date></row><row><issue>2016022</issue><redBalls>040919222330</redBalls><blueBall>07</blueBall><date>2016-02-28（日）</date></row><row><issue>2016023</issue><redBalls>030610192529</redBalls><blueBall>07</blueBall><date>2016-03-01（二）</date></row><row><issue>2016024</issue><redBalls>020507141831</redBalls><blueBall>13</blueBall><date>2016-03-03（四）</date></row><row><issue>2016025</issue><redBalls>041112172430</redBalls><blueBall>12</blueBall><date>2016-03-06（日）</date></row><row><issue>2016026</issue><redBalls>040912283033</redBalls><blueBall>01</blueBall><date>2016-03-08（二）</date></row><row><issue>2016027</issue><redBalls>111315171931</redBalls><blueBall>05</blueBall><date>2016-03-10（四）</date></row><row><issue>2016028</issue><redBalls>060812212529</redBalls><blueBall>01</blueBall><date>2016-03-13（日）</date></row><row><issue>2016029</issue><redBalls>121518202127</redBalls><blueBall>15</blueBall><date>2016-03-15（二）</date></row><row><issue>2016030</issue><redBalls>101419222529</redBalls><blueBall>12</blueBall><date>2016-03-17（四）</date></row><row><issue>2016031</issue><redBalls>030810192633</redBalls><blueBall>03</blueBall><date>2016-03-20（日）</date></row><row><issue>2016032</issue><redBalls>081214152127</redBalls><blueBall>15</blueBall><date>2016-03-22（二）</date></row><row><issue>2016033</issue><redBalls>061718202729</redBalls><blueBall>15</blueBall><date>2016-03-24（四）</date></row><row><issue>2016034</issue><redBalls>031521222328</redBalls><blueBall>15</blueBall><date>2016-03-27（日）</date></row><row><issue>2016035</issue><redBalls>041319202629</redBalls><blueBall>11</blueBall><date>2016-03-29（二）</date></row><row><issue>2016036</issue><redBalls>030407092022</redBalls><blueBall>03</blueBall><date>2016-03-31（四）</date></row><row><issue>2016037</issue><redBalls>061526313233</redBalls><blueBall>16</blueBall><date>2016-04-03（日）</date></row><row><issue>2016038</issue><redBalls>031213222829</redBalls><blueBall>03</blueBall><date>2016-04-05（二）</date></row><row><issue>2016039</issue><redBalls>010307181927</redBalls><blueBall>16</blueBall><date>2016-04-07（四）</date></row><row><issue>2016040</issue><redBalls>031319202326</redBalls><blueBall>03</blueBall><date>2016-04-10（日）</date></row><row><issue>2016041</issue><redBalls>121718212224</redBalls><blueBall>04</blueBall><date>2016-04-12（二）</date></row><row><issue>2016042</issue><redBalls>071417232631</redBalls><blueBall>09</blueBall><date>2016-04-14（四）</date></row><row><issue>2016043</issue><redBalls>051420263033</redBalls><blueBall>12</blueBall><date>2016-04-17（日）</date></row><row><issue>2016044</issue><redBalls>010310121830</redBalls><blueBall>01</blueBall><date>2016-04-19（二）</date></row><row><issue>2016045</issue><redBalls>040912173032</redBalls><blueBall>03</blueBall><date>2016-04-21（四）</date></row><row><issue>2016046</issue><redBalls>072025262730</redBalls><blueBall>14</blueBall><date>2016-04-24（日）</date></row><row><issue>2016047</issue><redBalls>020508151722</redBalls><blueBall>16</blueBall><date>2016-04-26（二）</date></row><row><issue>2016048</issue><redBalls>030813141530</redBalls><blueBall>04</blueBall><date>2016-04-28（四）</date></row><row><issue>2016049</issue><redBalls>060813142227</redBalls><blueBall>10</blueBall><date>2016-05-01（日）</date></row><row><issue>2016050</issue><redBalls>091224282930</redBalls><blueBall>02</blueBall><date>2016-05-03（二）</date></row><row><issue>2016051</issue><redBalls>010204091533</redBalls><blueBall>12</blueBall><date>2016-05-05（四）</date></row><row><issue>2016052</issue><redBalls>010613192428</redBalls><blueBall>16</blueBall><date>2016-05-08（日）</date></row><row><issue>2016053</issue><redBalls>020810122931</redBalls><blueBall>01</blueBall><date>2016-05-10（二）</date></row><row><issue>2016054</issue><redBalls>061116192832</redBalls><blueBall>04</blueBall><date>2016-05-12（四）</date></row><row><issue>2016055</issue><redBalls>050610162226</redBalls><blueBall>11</blueBall><date>2016-05-15（日）</date></row><row><issue>2016056</issue><redBalls>030408111618</redBalls><blueBall>14</blueBall><date>2016-05-17（二）</date></row><row><issue>2016057</issue><redBalls>071219222326</redBalls><blueBall>11</blueBall><date>2016-05-19（四）</date></row><row><issue>2016058</issue><redBalls>030518202432</redBalls><blueBall>11</blueBall><date>2016-05-22（日）</date></row><row><issue>2016059</issue><redBalls>041112202528</redBalls><blueBall>15</blueBall><date>2016-05-24（二）</date></row><row><issue>2016060</issue><redBalls>040522262932</redBalls><blueBall>08</blueBall><date>2016-05-26（四）</date></row><row><issue>2016061</issue><redBalls>050608182032</redBalls><blueBall>08</blueBall><date>2016-05-29（日）</date></row><row><issue>2016062</issue><redBalls>121315181921</redBalls><blueBall>09</blueBall><date>2016-05-31（二）</date></row><row><issue>2016063</issue><redBalls>161718232832</redBalls><blueBall>07</blueBall><date>2016-06-02（四）</date></row><row><issue>2016064</issue><redBalls>031214171926</redBalls><blueBall>03</blueBall><date>2016-06-05（日）</date></row><row><issue>2016065</issue><redBalls>131622252627</redBalls><blueBall>14</blueBall><date>2016-06-07（二）</date></row><row><issue>2016066</issue><redBalls>030713181920</redBalls><blueBall>05</blueBall><date>2016-06-09（四）</date></row><row><issue>2016067</issue><redBalls>091318202731</redBalls><blueBall>04</blueBall><date>2016-06-12（日）</date></row><row><issue>2016068</issue><redBalls>081923283132</redBalls><blueBall>01</blueBall><date>2016-06-14（二）</date></row><row><issue>2016069</issue><redBalls>081011202127</redBalls><blueBall>11</blueBall><date>2016-06-16（四）</date></row><row><issue>2016070</issue><redBalls>030611182329</redBalls><blueBall>01</blueBall><date>2016-06-19（日）</date></row><row><issue>2016071</issue><redBalls>192126282932</redBalls><blueBall>01</blueBall><date>2016-06-21（二）</date></row><row><issue>2016072</issue><redBalls>051619222425</redBalls><blueBall>02</blueBall><date>2016-06-23（四）</date></row><row><issue>2016073</issue><redBalls>091112151620</redBalls><blueBall>13</blueBall><date>2016-06-26（日）</date></row><row><issue>2016074</issue><redBalls>061011122025</redBalls><blueBall>12</blueBall><date>2016-06-28（二）</date></row><row><issue>2016075</issue><redBalls>010306162932</redBalls><blueBall>07</blueBall><date>2016-06-30（四）</date></row><row><issue>2016076</issue><redBalls>070813223032</redBalls><blueBall>01</blueBall><date>2016-07-03（日）</date></row><row><issue>2016077</issue><redBalls>010917192029</redBalls><blueBall>10</blueBall><date>2016-07-05（二）</date></row><row><issue>2016078</issue><redBalls>020408232629</redBalls><blueBall>02</blueBall><date>2016-07-07（四）</date></row><row><issue>2016079</issue><redBalls>010310122428</redBalls><blueBall>02</blueBall><date>2016-07-10（日）</date></row><row><issue>2016080</issue><redBalls>011617242532</redBalls><blueBall>14</blueBall><date>2016-07-12（二）</date></row><row><issue>2016081</issue><redBalls>020615253032</redBalls><blueBall>07</blueBall><date>2016-07-14（四）</date></row><row><issue>2016082</issue><redBalls>061214151720</redBalls><blueBall>09</blueBall><date>2016-07-17（日）</date></row><row><issue>2016083</issue><redBalls>091617243031</redBalls><blueBall>04</blueBall><date>2016-07-19（二）</date></row><row><issue>2016084</issue><redBalls>020412182426</redBalls><blueBall>05</blueBall><date>2016-07-21（四）</date></row><row><issue>2016085</issue><redBalls>011219202125</redBalls><blueBall>16</blueBall><date>2016-07-24（日）</date></row><row><issue>2016086</issue><redBalls>091011121532</redBalls><blueBall>05</blueBall><date>2016-07-26（二）</date></row><row><issue>2016087</issue><redBalls>020310111421</redBalls><blueBall>12</blueBall><date>2016-07-28（四）</date></row><row><issue>2016088</issue><redBalls>031416182533</redBalls><blueBall>15</blueBall><date>2016-07-31（日）</date></row><row><issue>2016089</issue><redBalls>010314303132</redBalls><blueBall>08</blueBall><date>2016-08-02（二）</date></row><row><issue>2016090</issue><redBalls>021317202126</redBalls><blueBall>07</blueBall><date>2016-08-04（四）</date></row><row><issue>2016091</issue><redBalls>040814222328</redBalls><blueBall>07</blueBall><date>2016-08-07（日）</date></row><row><issue>2016092</issue><redBalls>021315232429</redBalls><blueBall>06</blueBall><date>2016-08-09（二）</date></row><row><issue>2016093</issue><redBalls>060915172527</redBalls><blueBall>09</blueBall><date>2016-08-11（四）</date></row><row><issue>2016094</issue><redBalls>060710121831</redBalls><blueBall>10</blueBall><date>2016-08-14（日）</date></row><row><issue>2016095</issue><redBalls>010509121832</redBalls><blueBall>12</blueBall><date>2016-08-16（二）</date></row><row><issue>2016096</issue><redBalls>061314212224</redBalls><blueBall>16</blueBall><date>2016-08-18（四）</date></row><row><issue>2016097</issue><redBalls>061325262831</redBalls><blueBall>01</blueBall><date>2016-08-21（日）</date></row><row><issue>2016098</issue><redBalls>020825293132</redBalls><blueBall>06</blueBall><date>2016-08-23（二）</date></row><row><issue>2016099</issue><redBalls>011121232733</redBalls><blueBall>06</blueBall><date>2016-08-25（四）</date></row><row><issue>2016100</issue><redBalls>031022232729</redBalls><blueBall>04</blueBall><date>2016-08-28（日）</date></row><row><issue>2016101</issue><redBalls>010319243233</redBalls><blueBall>01</blueBall><date>2016-08-30（二）</date></row><row><issue>2016102</issue><redBalls>050810141730</redBalls><blueBall>13</blueBall><date>2016-09-01（四）</date></row><row><issue>2016103</issue><redBalls>010513192427</redBalls><blueBall>11</blueBall><date>2016-09-04（日）</date></row><row><issue>2016104</issue><redBalls>050911183031</redBalls><blueBall>04</blueBall><date>2016-09-06（二）</date></row><row><issue>2016105</issue><redBalls>081019272831</redBalls><blueBall>16</blueBall><date>2016-09-08（四）</date></row><row><issue>2016106</issue><redBalls>040513222530</redBalls><blueBall>04</blueBall><date>2016-09-11（日）</date></row><row><issue>2016107</issue><redBalls>061118262732</redBalls><blueBall>01</blueBall><date>2016-09-13（二）</date></row><row><issue>2016108</issue><redBalls>020307081926</redBalls><blueBall>16</blueBall><date>2016-09-15（四）</date></row><row><issue>2016109</issue><redBalls>091115162733</redBalls><blueBall>05</blueBall><date>2016-09-18（日）</date></row><row><issue>2016110</issue><redBalls>050728313233</redBalls><blueBall>08</blueBall><date>2016-09-20（二）</date></row><row><issue>2016111</issue><redBalls>020407141532</redBalls><blueBall>04</blueBall><date>2016-09-22（四）</date></row><row><issue>2016112</issue><redBalls>061214151825</redBalls><blueBall>12</blueBall><date>2016-09-25（日）</date></row><row><issue>2016113</issue><redBalls>011116172026</redBalls><blueBall>14</blueBall><date>2016-09-27（二）</date></row><row><issue>2016114</issue><redBalls>051620222729</redBalls><blueBall>09</blueBall><date>2016-09-29（四）</date></row><row><issue>2016115</issue><redBalls>060820222627</redBalls><blueBall>09</blueBall><date>2016-10-02（日）</date></row><row><issue>2016116</issue><redBalls>071820232731</redBalls><blueBall>13</blueBall><date>2016-10-04（二）</date></row><row><issue>2016117</issue><redBalls>031014172833</redBalls><blueBall>02</blueBall><date>2016-10-06（四）</date></row><row><issue>2016118</issue><redBalls>091422233133</redBalls><blueBall>14</blueBall><date>2016-10-09（日）</date></row><row><issue>2016119</issue><redBalls>091921303132</redBalls><blueBall>04</blueBall><date>2016-10-11（二）</date></row><row><issue>2016120</issue><redBalls>020506212528</redBalls><blueBall>09</blueBall><date>2016-10-13（四）</date></row><row><issue>2016121</issue><redBalls>020310232528</redBalls><blueBall>09</blueBall><date>2016-10-16（日）</date></row><row><issue>2016122</issue><redBalls>152223242829</redBalls><blueBall>08</blueBall><date>2016-10-18（二）</date></row><row><issue>2016123</issue><redBalls>070912142027</redBalls><blueBall>16</blueBall><date>2016-10-20（四）</date></row><row><issue>2016124</issue><redBalls>091521242732</redBalls><blueBall>10</blueBall><date>2016-10-23（日）</date></row></data>

对应的XQuery查询文件:LotteryAnalyze.xq

xquery version "3.0" encoding "GB2312";declare variable $records := doc("LotteryResults.xml")/data/row; (:设置数据源:)declare variable $startIssue := (2016001); (:设置起始的期号:)declare variable $endIssue := (2016155); (:设置结束的期号:)declare variable $resultRedBalls := (16,6,23,6); (:设置同时出现的红球,允许出现相同的元素,程序将忽略:)declare variable $resultBlueBall := (10); (:设置蓝球:)(:下面方法用于找出符合要求的记录.参数:records:代表查询的源记录resultRedBalls:需要包含的红球序列.注:如果未设置红球则默认匹配所有红球情况resultBlueBall:需要包含的蓝球序列.注:如果未设置蓝球,这默认匹配所有蓝球情况startIssue:开始查询的期数序号.注:如果未设置起始期号,则从第一期开始endIssue:结束查询的期数序号.注:如果未设置结束期号,则到最后一期结束:)declare function local:analyze($records as element(row)*,$resultRedBalls as xs:integer*,$resultBlueBall as xs:integer?,$startIssue as xs:integer?,$endIssue as xs:integer?) as element(results){<results>{let $redballsRegex := if(exists($resultRedBalls)) then(local:redballsRegex($resultRedBalls)) else("\d+")let $blueballValue := if(exists($resultBlueBall)) then(local:blueballValue($resultBlueBall)) else ("\d+")for $record as element(row) at $index in $recordswhere (if(exists($startIssue)) then($record/issue >= $startIssue) else (true())) and (if(exists($endIssue)) then($record/issue <= $endIssue) else (true())) and $record/redBalls/matches(.,$redballsRegex) and $record/blueBall/matches(.,$blueballValue)return ("",<record><index>{$index}</index>{$record/issue,$record/redBalls,$record/blueBall,$record/date}</record>),""}</results>};(:下面方法用于返回红球序列的正则表达式,并且对红球序列进行有效性验证.:)declare function local:redballsRegex($resultRedBalls as xs:integer+) as xs:string{let $distincts := distinct-values($resultRedBalls)return if(count($distincts) > 6) then (error(xs:QName("RedBallError"),"More than 6 balls!")) else (let $regex := for $distinct as xs:integer at $index in $distinctsorder by $distinct ascending empty greatestreturn if($distinct < 10 and $distinct > 0) then (concat("0",$distinct)) else (if($distinct > 33 or $distinct <= 0) then (error(xs:QName("RedBallError"),concat("Redball: '",$distinct,"' is invalid!"))) else (xs:string($distinct)))return concat("^(\d{2})*?",string-join($regex,"(\d{2})*?"),"(\d{2})*$"))};(:下面方法用于返回蓝球的表达形式,并对其有效性进行验证:)declare function local:blueballValue($blueball as xs:integer) as xs:string{if($blueball <= 0 or $blueball > 16) then (error(xs:QName("BlueBallError"),concat("BlueBall: '",$blueball,"' is invalid!"))) else (if($blueball < 10) then (concat("0",$blueball)) else (xs:string($blueball)))};(:下面的方法用于计算出现概率:)declare function local:getProbability($records as element(row)*,$resultRedBalls as xs:integer*,$resultBlueBall as xs:integer?,$startIssue as xs:integer?,$endIssue as xs:integer?) as xs:string{let $results := local:analyze($records,$resultRedBalls,$resultBlueBall,$startIssue,$endIssue)let $allRecords := local:analyze($records,(),(),$startIssue,$endIssue)return concat(substring(concat(count($results/record) div count($allRecords/record) * 100,""),1,6),"%")};(:下面方法用于显示查询条件及结果:)declare function local:showDetail($records as element(row)*,$resultRedBalls as xs:integer*,$resultBlueBall as xs:integer?,$startIssue as xs:integer?,$endIssue as xs:integer?){"-------Condition Start-------",local:showConditions($records,$resultRedBalls,$resultBlueBall,$startIssue,$endIssue),"---------Condition End--------",concat("Query Probability:",local:getProbability($records,$resultRedBalls,$resultBlueBall,$startIssue,$endIssue)),"",local:analyze($records,$resultRedBalls,$resultBlueBall,$startIssue,$endIssue)};(:下面的方法用于显示查询条件信息:)declare function local:showConditions($records as element(row)*,$resultRedBalls as xs:integer*,$resultBlueBall as xs:integer?,$startIssue as xs:integer?,$endIssue as xs:integer?) as xs:string{let $redBallsCondition := local:showRedBallsCondition($resultRedBalls)let $blueBallCondition := local:showBlueBallCondition($resultBlueBall)let $issueCondition := local:showIssueCondition($startIssue,$endIssue)return concat($redBallsCondition,$blueBallCondition,$issueCondition)};(:下面这个方法用于显示红球条件:)declare function local:showRedBallsCondition($resultRedBalls as xs:integer*) as xs:string{let $value := if(count($resultRedBalls) > 0) then(let $result :=(for $redball as xs:integer at $index in distinct-values($resultRedBalls)order by $redball ascendingreturn if($redball > 33 or $redball <= 0) then (error(xs:QName("RedBallError"),concat("RedBall: '",$redball,"' is invalid!"))) else(xs:string($redball)))return string-join($result," ")) else("All Conditions.")return concat("RedBalls Condition:",$value,"")};(:下面这个方法用于显示蓝球条件:)declare function local:showBlueBallCondition($resultBlueBall as xs:integer?) as xs:string{let $value := if(exists($resultBlueBall)) then(if($resultBlueBall <=0 or $resultBlueBall > 16) then (error(xs:QName("BlueBallError"),concat("BlueBall: '",$resultBlueBall,"' is invalid!"))) else ($resultBlueBall)) else("All Conditions.")return concat("BlueBall Condition:",$value,"")};(:下面这个方法用于显示起始/结束期号信息:)declare function local:showIssueCondition($startIssue as xs:integer?,$endIssue as xs:integer?) as xs:string{let $start := if(exists($startIssue)) then(xs:string($startIssue)) else ("All")let $end := if(exists($endIssue)) then(xs:string($endIssue)) else ("All")return concat("Issue: Start(",$start,") To End(",$end,")")};local:showDetail($records,$resultRedBalls,$resultBlueBall,$startIssue,$endIssue)

对于上面的这个XQuery文件可以对开始中的各个变量进行设置.

也可以将其设置为空序列.(空序列情况下将会忽略该条件,如将$resultRedBalls设置为空序列,这表示所有的红球情况都符合查询条件)

中间定义了很多的自定义方法.在自定义方法中又定义了对各参数范围的有效性验证.如红球序列中元素必须在1-33的范围等.

同时结合正则表达式来对红球的结果进行验证,由于存储结果的时候已经将结果做了升序处理所以在判断红球的时候很容易生成对应的正则表达式.

执行上面的例子得到的结果如下:

<?xml version="1.0" encoding="UTF-8"?>-------Condition Start------- RedBalls Condition:6 16 23BlueBall Condition:10Issue: Start(2016001) To End(2016155) ---------Condition End-------- Query Probability:0.8064% <results><record><index>1918</index><issue>2016019</issue><redBalls>061316172330</redBalls><blueBall>10</blueBall><date>2016-02-21（日）</date></record></results>

可以看出符合查询条件出现的几率在今年的范围内是0.8064%.

由于我这边所统计的数据是所有的开奖记录.所以对应的index=1918表示在第1918次开奖的时候出现了符合查询的结果.并列出对应的开奖结果.

总结:XQuery作为一门独立的语言在处理XML及序列等方面具有非常灵活和便捷的一面.

特别是在其引入了自定义函数/模块/FLWOR等概念后提升了其更加强大的功能