java - 我怎样才能使这个统计查询动态？

Question

我有这个用户表:

id(int) | created (datetime)

我正在显示执行以下操作的服务器的统计信息:- 你选择一个日期- 您可以选择是否要显示每日、每周或每月的统计信息(请参阅查询)

我通过这样做使查询非常静态:

WEEK(DATE_ADD(" + date + ", INTERVAL -2 WEEK) // Shows how many users that has been created from two weeks before current date to current date

现在我希望应该查看统计数据的管理员能够选择他想要显示多长时间前的统计数据。所以我需要使查询动态化。

这是我做的原始静态查询:

String sQuery = "SELECT " +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created) as totalNumberOfCreatedUsers, " +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE DAYOFYEAR(" + date + ") = DAYOFYEAR(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created)) as thisDaysNumberOfCreatedUsers, " +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE DAYOFYEAR(DATE_ADD(" + date + ", INTERVAL -1 DAY)) = DAYOFYEAR(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created)) as thisDaysMinusOneNumberOfCreatedUsers," +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE DAYOFYEAR(DATE_ADD(" + date + ", INTERVAL -2 DAY)) = DAYOFYEAR(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created)) as thisDaysMinusTwoNumberOfCreatedUsers," +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE DAYOFYEAR(DATE_ADD(" + date + ", INTERVAL -3 DAY)) = DAYOFYEAR(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created)) as thisDaysMinusThreeNumberOfCreatedUsers," +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE DAYOFYEAR(DATE_ADD(" + date + ", INTERVAL -4 DAY)) = DAYOFYEAR(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created)) as thisDaysMinusFourNumberOfCreatedUsers," +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE DAYOFYEAR(DATE_ADD(" + date + ", INTERVAL -5 DAY)) = DAYOFYEAR(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created)) as thisDaysMinusFiveNumberOfCreatedUsers," +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE DAYOFYEAR(DATE_ADD(" + date + ", INTERVAL -6 DAY)) = DAYOFYEAR(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created)) as thisDaysMinusSixNumberOfCreatedUsers," +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE DAYOFYEAR(DATE_ADD(" + date + ", INTERVAL -7 DAY)) = DAYOFYEAR(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created)) as thisDaysMinusSevenNumberOfCreatedUsers," +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE WEEK(" + date + ") = WEEK(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created))  as thisWeeksNumberOfCreatedUsers," +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE WEEK(DATE_ADD(" + date + ", INTERVAL -1 WEEK)) = WEEK(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created))  as thisWeeksMinusOneNumberOfCreatedUsers," +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE WEEK(DATE_ADD(" + date + ", INTERVAL -2 WEEK)) = WEEK(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created))  as thisWeeksMinusTwoNumberOfCreatedUsers," +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE WEEK(DATE_ADD(" + date + ", INTERVAL -3 WEEK)) = WEEK(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created))  as thisWeeksMinusThreeNumberOfCreatedUsers," +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE WEEK(DATE_ADD(" + date + ", INTERVAL -4 WEEK)) = WEEK(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created))  as thisWeeksMinusFourNumberOfCreatedUsers," +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE MONTH(" + date + ") = MONTH(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created))  as thisMonthsNumberOfCreatedUsers," +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE MONTH(DATE_ADD(" + date + ", INTERVAL -1 MONTH)) = MONTH(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created))  as thisMonthsMinusOneNumberOfCreatedUsers," +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE MONTH(DATE_ADD(" + date + ", INTERVAL -2 MONTH)) = MONTH(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created))  as thisMonthsMinusTwoNumberOfCreatedUsers," +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE MONTH(DATE_ADD(" + date + ", INTERVAL -3 MONTH)) = MONTH(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created))  as thisMonthsMinusThreeNumberOfCreatedUsers," +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE MONTH(DATE_ADD(" + date + ", INTERVAL -4 MONTH)) = MONTH(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created))  as thisMonthsMinusFourNumberOfCreatedUsers," +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE MONTH(DATE_ADD(" + date + ", INTERVAL -5 MONTH)) = MONTH(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created))  as thisMonthsMinusFiveNumberOfCreatedUsers," +
                "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE MONTH(DATE_ADD(" + date + ", INTERVAL -6 MONTH)) = MONTH(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created))  as thisMonthsMinusSixNumberOfCreatedUsers" +
                "";

例如:我可以做的一个解决方案(java):(但这会创建一个大查询，我想要一种更有效的方法来做到这一点)(顺便说一句，这个例子不是工作代码，只是我想给你的东西和想法什么解决方案)

---

管理员选择查看 20 天/周/月

String addToQuery = "";
int daysWeeksMonthsToLookBack = inputFromUser // inputFromUser = 20 in this example
for(int i=0; i<daysWeeksMonthsToLookBack; i++){
  addToQuery += "       (SELECT count(statistic_customer_created.id) FROM statistic_customer_created WHERE DAYOFYEAR(DATE_ADD(" + date + ", INTERVAL -" + i + " DAY)) = DAYOFYEAR(statistic_customer_created.created) AND YEAR(" + date + ") = YEAR(statistic_customer_created.created)) as thisDaysMinusFiveNumberOfCreatedUsers," +

}

有人对我如何更有效地做到这一点有建议吗？提前致谢

Answer 1

我会使用像这样的范围

SELECT count(s.id) 
FROM statistic_customer_created s
WHERE s.created >= {date calculated in Java}

通过取这些日期的差值，您可以获得 1 到 2 天前创建的所有日期。

最近 7 天创建的数量和上周创建的数量应该相同。