我对数据库和LibreOffice Base/HSQLDB相对陌生。我安装了最新的Base。我的自行车数据库有1个表"BikeDate",其中有4个字段,如下所示:
| RideID | RideDate | Bike | Miles |
1 10/2/97 Y22 15.6
....... with say 620 entries
我试图获得的是过去17年每月乘车次数的比较。我的指标是所有年份中每个月的所有骑行次数之和,然后是所有年份每个月的平均骑行次数,然后是2014年每个月的英里数之和和和2014年每个每月的平均骑行量。
这就是我目前所拥有的:
SELECT MONTH( "RideDate" ) AS "MONTH", SUM( "Miles" ) AS "SUM of Miles", AVG( "Miles" ) AS "AVG of Miles",
( SELECT SUM( "Miles" ) FROM "BikeDate" WHERE ( YEAR( "RideDate" ) = '2014' ) ORDER BY MONTH( "RideDate" ) ) AS "Sum 2014",
( SELECT AVG( "Miles" ) FROM "BikeDate" WHERE ( YEAR( "RideDate" ) = '2014' ) ORDER BY MONTH( "RideDate" ) ) AS "Avg 2014"
FROM "BikeDate" AS "BikeDate"
GROUP BY MONTH( "RideDate" )
ORDER BY MONTH( "RideDate" ) ASC
输出:
|MONTH | Sum of Miles | AVG of Miles | Sum 2014 | AVG 2014 |
2 | 12.2 | 6.1 | 29 | 14.5 |
3 | 217.9 | 10.38 | 29 | 14.5 |
4 | 744.3 | 12.2 | 29 | 14.5 |
5 | 1316.3 | 17.55 | 29 | 14.5 |
....
12 | 70.2 | 11.7 | 29 | 14.5 |
第一:这能做到吗?2014年第二个月的两个栏目都应该为零(我们有48"的降雪量,目前总共338")。我怎样才能让它发挥作用?我想留在LO基地,因为它是免费的,目前已经安装。
谢谢,戴夫。
与LO Base捆绑在一起的HSQLDB 1.8数据库似乎很难实现这一点。您可以使用最新的HSQLDB 2.3.x作为带有LO的外部数据库。此版本允许您在当前年份使用以下表达式:
AVG ("Miles") FILTER (WHERE YEAR("RideDate") = 2014)
您可以将此表达式和类似的SUM表达式与主选择中的SUM和AVG表达式一起使用。不需要使用额外的子选择查询。
SELECT MONTH( "RideDate" ) AS "MONTH",
SUM( "Miles" ) AS "SUM of Miles",
AVG( "Miles" ) AS "AVG of Miles",
SUM( "Miles" ) FILTER (WHERE YEAR("RideDate") = 2014) AS "Sum 2014",
AVG( "Miles" ) FILTER (WHERE YEAR("RideDate") = 2014) AS "Avg 2014"
FROM "BikeDate" AS "BikeDate"
GROUP BY MONTH( "RideDate" )
ORDER BY MONTH( "RideDate" ) ASC
检查http://www.oooforum.org以及用于";拆分数据库";使用最新HSQLDB和LO的解决方案。