mysql - 使用 MySQL 和/或 R 在多个日期范围内有效地汇总数据

Question

我一直在尝试(并寻找)一种方法来对每个 ID 的日期范围求和。我到处都在寻找 SQL 语法来执行此操作，因为在我看来这应该是一个相当普遍的问题，但我还没有找到完全我想要的东西，因为示例 this .

我有一个格式如下的表格:

ID | start_date | end_date
---|---|---
aaa|2016-10-26 07:00| 2016-12-15 04:00
aaa|2016-10-26 08:00| 2016-12-15 05:00
bbb|2016-10-26 07:00| 2016-11-15 03:00
ccc|2016-10-26 07:00| 2016-10-30 04:00

可以说该表具有作为 PK 的 ID 和 start_date。 (此表有约 150 万条记录)。我们可以将此表中的每一行称为“句点”。

我还有一个日历，它在表格中标记了所有“工作时间段”，如下所示:(这个表有大约 800 条记录)

|end_date|
|-|
|2016-10-26 07:00|
|2016-10-26 08:00|
|2016-10-26 09:00|
|...|
|2016-12-26 09:00|

最后，我有一个包含消费数据的表格，如下所示:(该表有约 230 万条记录)。

|ID|start_date|consumption|
|-|-|-|
|aaa|2016-10-27 07:00| 1|
|aaa|2016-10-27 08:00| 5|
|aaa|2016-10-27 09:00| 3|
|bbb|2016-10-27 07:00| 3|

我想得到的结果是下表:

|ID|start_date|end_date|consumption_sum|
|-|-|-|-|
|aaa|2016-10-26 07:00| 2016-12-15 04:00|14|
|bbb|2016-10-26 07:00| 2016-11-15 03:00|32|
|ccc|2016-10-26 07:00| 2016-10-30 04:00|17|

其中consumption_sum = sum(start_date和end_date之间工作日所有消费数据的每个ID的消费)。这意味着:我想要每个 ID 每个周期内的消费总和。

现在，我正在前两个表之间进行某种连接，以获得一个大(太大)表，其中包含每个“期间”的每一天的扩展行。像下面这样:

|ID|start_date|end_date_s|
|-|-|-|
|aaa|2016-10-26 07:00| 2016-10-26 07:00|
|aaa|2016-10-26 07:00| 2016-10-26 08:00|
|aaa|2016-10-26 07:00| 2016-10-26 09:00|
|aaa|2016-10-26 07:00| 2016-10-26 10:00|
|aaa|2016-10-26 07:00| ...|
|aaa|2016-10-26 07:00| 2016-12-15 04:00|
|bbb|2016-10-26 07:00| 2016-10-26 07:00|
|bbb|2016-10-26 07:00| 2016-10-26 08:00|
|bbb|2016-10-26 07:00| 2016-10-26 09:00|
|bbb|2016-10-26 07:00| ...|
|bbb|2016-10-26 07:00| 2016-11-15 03:00|
|ccc|2016-10-26 07:00| 2016-10-26 07:00|
|ccc|2016-10-26 07:00| ...|

然后，将此表与第三个表合并以获得每天 end_date_s 的消耗量。

最后，我通过 (ID, start_date) 求和以获得所需的表。

我在 R 中通过名为 foverlaps 的 data.table 函数执行此操作，这是迄今为止我发现的最好的函数。这工作正常。

遗憾的是，在连接前两个表时，数据的大小使我的电脑内存不足。

我想知道(我很确定这是可能的)是否可以做得更好(通过 R 或 MySQL，我不在乎)。

我目前使用的R代码如下(示例数据有误，但至少你能看到代码):

library(data.table)
library(magrittr)

  stocks_periodo <-
    structure(list(CODIGO_REFERENCIA = c("5293cb5478d6d400f0f555d531f2d63b", 
    "0fe0b44806573de5bde3c200455f5f03", "eb57daacff2abadf0f4551386f3c6678", 
    "2ead409e514f379fec7c94504f79206b", "cd0f1f709ed6631aeaf00881fc43ccad"
    ), PERIODO_INI = structure(c(1477512000, 1477512000, 1477512000, 
    1477512000, 1477512000), class = c("POSIXct", "POSIXt"), tzone = "Europe/Paris"), 
        PERIODO_FIN = structure(c(1477533600, 1477533600, 1477533600, 
        1477533600, 1477533600), class = c("POSIXct", "POSIXt"), tzone = "Europe/Paris")), class = "data.frame", .Names = c("CODIGO_REFERENCIA", 
    "PERIODO_INI", "PERIODO_FIN"), row.names = c(NA, -5L)) %>% data.table(key="PERIODO_INI,PERIODO_FIN")


  calendario_n <- 
    structure(list(PERIODO_INI = structure(c(1477512000, 1477515600, 
    1477519200, 1477522800, 1477526400), class = c("POSIXct", "POSIXt"
    ), tzone = "Europe/Paris"), PERIODO_FIN = structure(c(1477512000, 
    1477515600, 1477519200, 1477522800, 1477526400), class = c("POSIXct", 
    "POSIXt"), tzone = "Europe/Paris")), .Names = c("PERIODO_INI", 
    "PERIODO_FIN"), row.names = c(NA, 5L), class = "data.frame") %>% data.table(key="PERIODO_INI,PERIODO_FIN")

  consumos <- 
    structure(list(PERIODO = structure(c(1478034000, 1478037600, 
    1478041200, 1478044800, 1478048400), class = c("POSIXct", "POSIXt"
    ), tzone = ""), CODIGO_REFERENCIA = c("f3bcfd70cc0c3434d96278c0cfee1df4", 
    "f3bcfd70cc0c3434d96278c0cfee1df4", "f3bcfd70cc0c3434d96278c0cfee1df4", 
    "f3bcfd70cc0c3434d96278c0cfee1df4", "f3bcfd70cc0c3434d96278c0cfee1df4"
    ), DIARIO_CONSUMOS = c(8L, 8L, 8L, 8L, 8L)), class = "data.frame", .Names = c("PERIODO", 
    "CODIGO_REFERENCIA", "DIARIO_CONSUMOS"), row.names = c(NA, -5L
    )) %>% data.table(key="CODIGO_REFERENCIA,PERIODO")

  consumos_futuros<-
    foverlaps(calendario_n, stocks_periodo, nomatch=0L) %>%
    select(-i.PERIODO_INI, -PERIODO_FIN) %>% 
    rename(PERIODO_FIN= i.PERIODO_FIN) %>% 
    data.table(key="CODIGO_REFERENCIA,PERIODO_INI,PERIODO_FIN")

  stocks_periodo %<>% 
    data.table(key=c("CODIGO_REFERENCIA", "PERIODO_INI", "PERIODO_FIN"))

  consumos_futuros <- consumos_futuros[!stocks_periodo]

  consumos_futuros %<>% 
    rename(PERIODO= PERIODO_FIN) %>% 
    data.table(key="CODIGO_REFERENCIA,PERIODO") %>% 
    merge(consumos) %>% 
    group_by(CODIGO_REFERENCIA, PERIODO_INI) %>% 
    summarize(CONSUMO_TOTAL= sum(DIARIO_CONSUMOS)) %>% 
    data.table 

在此先感谢(如果确实在其他地方回答了这个问题，我们深表歉意)。

Answer 1

不确定这是否真的解决了“一般”情况，但至少它解决了我的问题。

如第一个表所示，同一个 ID 有很多滚动周期。这些是通过将固定数量的小时数(基于 ID)与一组连续的 start_date 相加以生成一组 end_date 来构建的。

不知道为什么我没有早点想到这个，但是 zoo 包有 rollsum 和 rollapply 函数。这正是我想要的:给定大小的滚动总和。

因此，我没有对由 start_date 和 end_date 定义的任意时间段求和，而是根据 ID 对由大小参数给定的行数求和。

我只需要小心为每个工作时间段排成一行，不管它是否有消耗。

作为我结束时所做的一个例子，我保留了原始代码的编辑，我添加了一个新表 referencias，其中包括工作时隙的数量以求和消耗 (STOCK_HORAS)。

library(data.table)
library(magrittr)
library(dplyr)
library(zoo)
library(tidyr)

stocks_periodo <-
  structure(list(CODIGO_REFERENCIA = c("5293cb5478d6d400f0f555d531f2d63b", 
                                       "0fe0b44806573de5bde3c200455f5f03", "eb57daacff2abadf0f4551386f3c6678", 
                                       "2ead409e514f379fec7c94504f79206b", "cd0f1f709ed6631aeaf00881fc43ccad"
  ), PERIODO_INI = structure(c(1477512000, 1477512000, 1477512000, 
                               1477512000, 1477512000), class = c("POSIXct", "POSIXt"), tzone = "Europe/Paris"), 
  PERIODO_FIN = structure(c(1477533600, 1477533600, 1477533600, 
                            1477533600, 1477533600), class = c("POSIXct", "POSIXt"), tzone = "Europe/Paris")), class = "data.frame", .Names = c("CODIGO_REFERENCIA", 
                                                                                                                                                "PERIODO_INI", "PERIODO_FIN"), row.names = c(NA, -5L)) %>% data.table(key="PERIODO_INI,PERIODO_FIN")


calendario_n <- 
  structure(list(PERIODO_INI = structure(c(1477512000, 1477515600, 
                                           1477519200, 1477522800, 1477526400), class = c("POSIXct", "POSIXt"
                                           ), tzone = "Europe/Paris"), PERIODO_FIN = structure(c(1477512000, 
                                                                                                 1477515600, 1477519200, 1477522800, 1477526400), class = c("POSIXct", 
                                                                                                                                                            "POSIXt"), tzone = "Europe/Paris")), .Names = c("PERIODO_INI", 
                                                                                                                                                                                                            "PERIODO_FIN"), row.names = c(NA, 5L), class = "data.frame") %>% data.table(key="PERIODO_INI,PERIODO_FIN")

consumos <- 
  structure(list(PERIODO = structure(c(1478034000, 1478037600, 
                                       1478041200, 1478044800, 1478048400), class = c("POSIXct", "POSIXt"
                                       ), tzone = ""), CODIGO_REFERENCIA = c("f3bcfd70cc0c3434d96278c0cfee1df4", 
                                                                             "f3bcfd70cc0c3434d96278c0cfee1df4", "f3bcfd70cc0c3434d96278c0cfee1df4", 
                                                                             "f3bcfd70cc0c3434d96278c0cfee1df4", "f3bcfd70cc0c3434d96278c0cfee1df4"
                                       ), DIARIO_CONSUMOS = c(8L, 8L, 8L, 8L, 8L)), class = "data.frame", .Names = c("PERIODO", 
                                                                                                                     "CODIGO_REFERENCIA", "DIARIO_CONSUMOS"), row.names = c(NA, -5L
                                                                                                                     )) %>% data.table(key="CODIGO_REFERENCIA,PERIODO")
referencias <- structure(list(CODIGO_REFERENCIA = c("3bed628bf8f6242c28d88200faa7e869", 
                                                    "cb3b6727071ec6659c712c3ec99c873a", "84bf5c06deaa2e42ae7edf6055b490db", 
                                                    "bb10cc5b0c3e127bd2073336365bf0e5", "b8b71160125f95a104e24878ff651e9c", 
                                                    "564169fbe71a04b31bb8e141be3fac66"), STOCK_HORAS = c(14, 14, 
                                                                                                         14, 18, 18, 14)), .Names = c("CODIGO_REFERENCIA", "STOCK_HORAS"
                                                                                                         ), class = c("data.table", "data.frame"), row.names = c(NA, -6L
                                                                                                         )) %>% data.table

calendario_refs <- 
  CJ(PERIODO= calendario_n %>% select(PERIODO=PERIODO_INI) %>% first,
     CODIGO_REFERENCIA= referencias %>% select(CODIGO_REFERENCIA) %>% first)

# por alguna razón rollsum va rápido pero no tiene partial =TRUE. Esto hace que
# valga la pena partir los cálculos en 2 y luego juntarlos.

rollsum_1 <-
  calendario_refs %>% 
  merge(consumos, by=c("CODIGO_REFERENCIA", "PERIODO"), all.x=TRUE) %>% 
  merge(referencias, by="CODIGO_REFERENCIA") %>% 
  replace_na(list(DIARIO_CONSUMOS=0)) %>% 
  select(CODIGO_REFERENCIA, PERIODO, STOCK_HORAS, DIARIO_CONSUMOS) %>% 
  arrange(CODIGO_REFERENCIA, PERIODO) %>% 
  group_by(CODIGO_REFERENCIA) %>%
  # si tenemos menos periodos que la suma móvil: reajustamos a lo máximo
  mutate(STOCK_HORAS= pmin(STOCK_HORAS, n())) %>%
  mutate(CONSUMO_TOTAL= rollsum(DIARIO_CONSUMOS, first(STOCK_HORAS), align = "left", fill=NA)) %>%
  data.table