Question

查询返回每日数据以进行可变频率聚合

2021-07-14

104

mysql sql

我们有一个数据库，用于存储汇总的预算交易，并以一周为结束日期。这些交易的频率各不相同（例如每周、每两周、每三周、每四周）。它在 Project 数据库表中由“report_frequency”字段表示。

我正在尝试编写一个 SQL 查询，将所有数据分离到每日。如果我使用脚本执行此操作，我当然会先检查项目报告频率，然后再进行计算。但是，我想将此 SQL 语句放入 Power Bi 中以用于报告目的。如果无法通过 SQL 语句完成此操作，我愿意听取任何建议。

我找到了接近的解决方案：

但是，这些都没有考虑到聚合数据的可变天数。

这是我对最小示例：

数据库

DROP SCHEMA IF EXISTS `frequency_test` ;
CREATE SCHEMA IF NOT EXISTS `frequency_test` DEFAULT CHARACTER SET utf8 ;
USE `frequency_test`;

DROP TABLE IF EXISTS `project`;
CREATE TABLE `project` (
  `project_id` int NOT NULL AUTO_INCREMENT,
  `report_frequency` INTEGER NOT NULL,
  PRIMARY KEY (`project_id`)
);

LOCK TABLES `project` WRITE;
/*!40000 ALTER TABLE `project` DISABLE KEYS */;
INSERT INTO `project` VALUES (1,14);
INSERT INTO `project` VALUES (2,7);
/*!40000 ALTER TABLE `project` ENABLE KEYS */;
UNLOCK TABLES;

DROP TABLE IF EXISTS `budget`;
CREATE TABLE `budget` (
  `budget_id` int NOT NULL AUTO_INCREMENT,
  `project_id` int NOT NULL,
  `entry_date` date NOT NULL,
  `hours` decimal(9,2) NOT NULL,
  `dollars` decimal(9,2) NOT NULL,
  PRIMARY KEY (`budget_id`),
  KEY `project_id` (`project_id`)
);

LOCK TABLES `budget` WRITE;
/*!40000 ALTER TABLE `budget` DISABLE KEYS */;
INSERT INTO `budget` VALUES (1,1,'2021-07-18',80,1000);
INSERT INTO `budget` VALUES (2,1,'2021-08-01',80,1500);
INSERT INTO `budget` VALUES (3,2,'2021-07-04',60,600);
INSERT INTO `budget` VALUES (4,2,'2021-08-11',40,800);
/*!40000 ALTER TABLE `budget` ENABLE KEYS */;
UNLOCK TABLES;

查询 7 天（使用上面的第一个链接）

SELECT
-- project data
p.project_id,
p.report_frequency,
-- period data
b.budget_id,
b.entry_date,
b.hours,
b.dollars,
-- calculated data
entry_date + INTERVAL d.day DAY daily_date,
hours / (7 * p.report_frequency / 7) AS daily_hours,
dollars / (7 * p.report_frequency / 7) AS daily_dollars
FROM budget b
CROSS JOIN (SELECT 0 AS day
            UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
            UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) d
JOIN project p ON p.project_id = b.project_id;

查询 n 天（使用上面的第二个链接） - 不起作用。 错误：错误代码：1054。'where 子句' 中的未知列 'p.frequency'

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n 
UNION ALL SELECT 1  
UNION ALL SELECT 2  
UNION ALL SELECT 3  
UNION ALL SELECT 4
UNION ALL SELECT 5  
UNION ALL SELECT 6   
UNION ALL SELECT 7
UNION ALL SELECT 8  
UNION ALL SELECT 9   
UNION ALL SELECT 10 
UNION ALL SELECT 11 
UNION ALL SELECT 12
UNION ALL SELECT 13
UNION ALL SELECT 14 
UNION ALL SELECT 15;

CREATE OR REPLACE VIEW generator_256
AS SELECT ( ( hi.n << 4 ) | lo.n ) AS n
     FROM generator_16 lo, generator_16 hi;
     
SELECT
-- project data
p.project_id,
p.report_frequency,
-- period data
b.budget_id,
b.entry_date,
b.hours,
b.dollars,
-- calculated data
entry_date + INTERVAL d.day DAY daily_date,
hours / (7 * p.report_frequency / 7) AS daily_hours,
dollars / (7 * p.report_frequency / 7) AS daily_dollars
FROM budget b
CROSS JOIN (SELECT n
  FROM generator_256
 WHERE n < p.frequency) d
JOIN project p ON p.project_id = b.project_id;

Answer 1

您需要将 WHERE 子句移到外面（并且它应该是 p.report_frequency 而不是 p.frequency ）：

SELECT
    b.project_id,
    b.budget_id,
    b.entry_date,
    b.entry_date + INTERVAL d.day DAY daily_date,
    b.hours / (7 * p.report_frequency / 7) AS daily_hours,
    b.dollars / (7 * p.report_frequency / 7) AS daily_dollars
FROM budget b JOIN project p ON p.project_id = b.project_id
CROSS JOIN (SELECT n day
  FROM generator_256) d
WHERE d.day < p.report_frequency  
ORDER BY `b`.`budget_id`, `daily_date` ASC;

据此您需要修改 daily_hours 和 daily_dollars 列（如果它们依赖于 p.report_frequency

）

Answer 2

我不确定这是否是您想要的。要在 MySql >= 8.0 中生成从 0 到 N 的天数，您可以使用递归查询。

您需要将我的查询中的 15 更改为您想要的最大数字。

WITH RECURSIVE days AS (SELECT 0 AS n
                     
                        UNION ALL 
                     
                        SELECT n + 1
                        FROM days
                        WHERE n < 15)
                     
                     
SELECT *
FROM days;

在您的 7 天查询 中使用它：

WITH RECURSIVE days AS (SELECT 0 AS n
                     
                        UNION ALL 
                     
                        SELECT n + 1
                        FROM days
                        WHERE n < 6)
                        
                        
SELECT
-- project data
p.project_id,
p.report_frequency,
-- period data
b.budget_id,
b.entry_date,
b.hours,
b.dollars,
-- calculated data
entry_date + INTERVAL days.n DAY daily_date,
hours / (7 * p.report_frequency / 7) AS daily_hours,
dollars / (7 * p.report_frequency / 7) AS daily_dollars
FROM budget b
CROSS JOIN days
JOIN project p ON p.project_id = b.project_id;