我们必须每周运行一个sql server查询，考虑从最晚周六00:01到周六23:59（科威特时间）的帐户事务。这个过程考虑了数千行，因此非常繁重。我决定分两步做，主要是因为我无法把它变成一个单一的步骤；）
我首先必须将每个事务的sql server日期列偏移为时间。
因为我通过php使用这个脚本，所以我分两步进行：首先定义最新的星期六日期范围（例如，如果查询在星期六运行，它将计算前一个星期六的范围），然后为第二个脚本使用变量。
我的第一个问题是上周六：
SELECT
(datetime, CASE
WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) = 'Sunday'
THEN DATEADD(DAY, -1, CAST(CURRENT_TIMESTAMP AS date))
WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) = 'Monday'
THEN DATEADD(DAY, -2, CAST(CURRENT_TIMESTAMP AS date))
WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) = 'Tuesday'
THEN DATEADD(DAY, -3, CAST(CURRENT_TIMESTAMP AS date))
WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) = 'Wednesday'
THEN DATEADD(DAY, -4, CAST(CURRENT_TIMESTAMP AS date))
WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) = 'Thursday'
THEN DATEADD(DAY, -5, CAST(CURRENT_TIMESTAMP AS date))
WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) = 'Friday'
THEN DATEADD(DAY, -6, CAST(CURRENT_TIMESTAMP AS date))
WHEN DATENAME(weekday, CAST(CURRENT_TIMESTAMP AS date)) = 'Saturday'
THEN DATEADD(DAY, -0, CAST(CURRENT_TIMESTAMP AS date))
END) at time zone 'Arab Standard Time' AS start_time,
CONVERT(datetime,
CASE
WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Sunday' THEN dateadd(day,0,cast(CURRENT_TIMESTAMP AS date))
WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Monday' THEN dateadd(day,-1,cast(CURRENT_TIMESTAMP AS date))
WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Tuesday' THEN dateadd(day,-2,cast(CURRENT_TIMESTAMP AS date))
WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Wednesday' THEN dateadd(day,-3,cast(CURRENT_TIMESTAMP AS date))
WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Thursday' THEN dateadd(day,-4,cast(CURRENT_TIMESTAMP AS date))
WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Friday' THEN dateadd(day,-5,cast(CURRENT_TIMESTAMP AS date))
WHEN datename(weekday,cast(CURRENT_TIMESTAMP AS date)) = 'Saturday' THEN dateadd(day,-6,cast(CURRENT_TIMESTAMP AS date))
END) at time zone 'Arab Standard Time' as end_time然后在另一个查询中插入来自上述查询的变量$start\u date和$end\u date，以获得最终结果：
SELECT
USERID,
DEPOSIT,
RMwin,
CASE
WHEN DEPOSIT > 0
AND RMwin <= DEPOSIT
AND RMwin > 0 THEN
RMWIN * 0.3
WHEN RMwin > DEPOSIT
AND DEPOSIT > 0 THEN
DEPOSIT *.3
WHEN RMwin <= 0 THEN
0 ELSE 0
END AS CB,
CASE
WHEN DEPOSIT + RMwin = 0 THEN
0 ELSE 1
END AS check_,
FORMAT ( min_lt, 'dd/MM/yy HH:mm' ) AS min_lt,
format ( min_kuw, 'dd/MM/yy HH:mm' ) AS min_kuw,
format ( max_lt, 'dd/MM/yy HH:mm' ) AS max_lt,
format ( max_kuw, 'dd/MM/yy HH:mm' ) AS max_kuw,
min_id,
max_id
FROM
(
SELECT
UserID,
SUM ( DEPOSIT ) AS DEPOSIT,
SUM ( RMwin ) AS RMwin,
MIN ( AccountTranID ) AS min_id,
MAX ( AccountTranID ) AS max_id,
MIN ( local_time ) AS min_lt,
MIN ( kuwait_date ) AS min_kuw,
MAX ( local_time ) AS max_lt,
MAX ( kuwait_date ) AS max_kuw
FROM
(
SELECT
AccountTranID,
TranType,
UserID,
datetime,
datetime AT TIME ZONE 'Romance Standard Time' AS local_time,
datetime AT TIME ZONE 'Romance Standard Time' AT TIME ZONE 'Arab Standard Time' AS kuwait_date,
CASE
TranType
WHEN 'GAME_B' THEN
- AmountReal
WHEN 'GAME_W' THEN
- AmountReal
WHEN 'REFUND' THEN
- AmountReal ELSE 0
END AS RMwin,
CASE
WHEN TranType = 'DEPOSIT' THEN
AmountReal ELSE 0
END AS DEPOSIT
FROM
admin_all.DataFeed
WHERE
datetime AT TIME ZONE 'Romance Standard Time' AT TIME ZONE 'Arab Standard Time' >= ( convert(datetime,'".$start_date."') AT TIME ZONE 'Arab Standard Time' )
AND datetime AT TIME ZONE 'Romance Standard Time' AT TIME ZONE 'Arab Standard Time' < ( convert(datetime,'".$end_date."') AT TIME ZONE 'Arab Standard Time' )
) trans
GROUP BY
UserID
) ok
ORDER BY
CB DESC不幸的是，这个查询对于我们的服务器资源来说非常繁重，我正试图找到一种方法来简化它以达到相同的结果。
有没有人能找到解决办法？
3条答案
按热度按时间cyej8jka1#
8qgya5xd2#
您的第一个查询可以这样简单（例如2020年8月的（test）输出）：
输出：
mtb9vblg3#
考虑
DATEPART对于工作日数字，应用算术检索相对于当前日期的上周六到上周日。下面两个表达式可以替换长CASE第一个查询语句：从那里，很容易集成到大型查询的
WHERE条款：PHP
在php中运行时，考虑参数化，甚至可以在php中计算日期（不需要单独的sql查询，只返回常量）：