如何按周分组以指定工作日结束? [英] How to group by week specifying end of week day?

问题描述

我需要运行按周分组的报告.这可以通过使用group by week(date)来完成，但是客户端希望设置标记星期几的星期几.因此可以是星期二，星期三等.如何通过查询将其分为一组?

I need to run a report grouped by week. This could be done by using group by week(date) but the client wants to set the day of the week that marks the end of week. So it can be Tuesday, Wednesday etc. How can I work this into a group by query?

datetime列类型为unix时间戳.

The datetime column type is unix timestamp.

推荐答案

The WEEK() function takes an optional second parameter to specify the start of the week:

此函数返回日期的星期数. WEEK()的两个参数形式使您可以指定星期是从星期日还是星期一开始，以及返回值应在0到53或从1到53的范围内.

This function returns the week number for date. The two-argument form of WEEK() enables you to specify whether the week starts on Sunday or Monday and whether the return value should be in the range from 0 to 53 or from 1 to 53.

但是，只能将其设置为星期日或星期一.

However, it can only be set to Sunday or Monday.

更新:除了下面的注释，您还可以考虑根据注释中的建议，根据WEEK(DATE_ADD(date INTERVAL x DAY))向表中添加新列以作为分组字段.您可能需要创建触发，以在任何时候自动生成此值date字段以及插入新行时将进行更新.然后，您将可以根据需要在此新字段上创建可用索引.

UPDATE: Further to the comments below, you may want to consider adding a new column to your table to act as a grouping field, based on WEEK(DATE_ADD(date INTERVAL x DAY)), as suggested in the comments. You may want to create triggers to automatically generate this values whenever the date field is updated, and when new rows are inserted. You would then be able to create a usable index on this new field as required.

这篇关于如何按周分组以指定工作日结束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！