主页 / coding / 问题 / 79245148
Accepted
user3466413
Asked: 2024-12-03 02:16:53 +0800 CST

如何在 Google 表格中执行基本的日期分组操作？

  • 772

我有如下数据（示例）：

胶原蛋白 胶束B 科尔
值 1 值 2 1/4/24 下午 4:15
值 3 值 4 1/4/24 下午 6:30
值 5 值 6 1/5/24 下午 2:30
值 7 值 8 1/5/24 下午 3:30
价值 9 价值 10 1/5/24 下午 5:30

我正在尝试做一些在正确的 SQL 中应该非常简单的事情：“对于表中的每个日期 Col C（而不是日期/时间）Col A ，找出该日期最早和最晚事件之间经过的小时数（包括小数小时数）。”和中的值Col B不相关。

最终结果是我希望它能回来，或者类似的东西（这里的评论只是为了易读性）：

日期 小时 评论
1/4/24 2.25 下午 6:30 — 下午 4:15
1/5/24 3 下午 5:30 - 下午 2:30，因为下午 3:30 不是最短时间或最长时间

在 SQL 中，我猜测查询将是这样的，带有一些伪代码：

SELECT
   number_of_hours(MAX(col_c) - MIN(col_c))
FROM
   my_table
GROUP BY
   DATE(col_c)

我不知道如何在 Google 表格中执行此操作。鉴于我认为这个问题更像是数据库问题，而不是您在电子表格中执行的操作，我尝试了该QUERY函数，但我所做的一切都以无效查询结束。我认为 Google 表格不允许您提取日期/时间的日期部分并对其进行聚合。

我尝试过但失败的事情：

  • =query(A:C, "SELECT A, B, C, " & TEXT(C, "yyyy-mm-dd"), 1)：公式解析错误
  • =query(A:C, "SELECT A, B, C, " & DATE(C), 1)：公式解析错误
  • =query(A:C, "SELECT C, COUNT(B) GROUP BY " & DATE(C), 1)：公式解析错误
  • =query(A:C, "SELECT C, COUNT(B) GROUP BY DATE(C)", 1)：无法解析函数 QUERY 参数 2 的查询字符串：PARSE_ERROR：在第 1 行第 33 列遇到“”（“”（“”）
  • =query(A:C, "SELECT C, DATE '" & YEAR(C) & "-" & (MONTH(C) + 1) & "-" & DAY(C) & "'", 1)：公式解析错误
  • =query(A:C, "SELECT C, " & DATEVALUE(YEAR(C) & "-" & (MONTH(C) + 1) & "-" & DAY(C)), 1)：公式解析错误
  • =query(A:C, "SELECT C, DATE(YEAR(C), MONTH(C), DAY(C))", 1)：无法解析函数 QUERY 参数 2 的查询字符串：PARSE_ERROR：在第 1 行第 15 列遇到“”（“”（“”）

那么我该如何做呢？

date

2 个回答

  1. Best Answer

    您可以使用：

    =SORT(LET(
   c,TOCOL(C2:C,1),
   d,TEXT(c,"m/d/yy"),
   MAP(UNIQUE(d),LAMBDA(u,LET(
    f,FILTER(c,d=u)*24,
    {u,MAX(f)-MIN(f)})))))
    • 2

  2. 处理电子表格日期和Google 查询日期时存在一些问题：

    • 查询中的日期对象不支持差值之类的算术运算。这导致很难计算实际差值。

    • Google 查询的日期对象的纪元是 1970 年 1 月 1 日，而电子表格上的起始日期是 1899 年 12 月 30 日。相差 25569 天

    • 查询将日期表示为从纪元开始的毫秒数，而表格则将其表示为从 1899 年 12 月 30 日起的天数。

    我们通过更改工作表日期TO PURE NUMBERs（天），然后将其转换为从纪元（-25569）开始的millis秒，然后对其使用查询来克服这些问题：

    =LET(
  days2ms,24*60*60*1000,
  diff, "(max(Col1)-min(Col1))/"&days2ms,
  QUERY(
    ARRAYFORMULA((TO_PURE_NUMBER(C1:C5)-25569)*days2ms),
    "Select 
      toDate(Col1),
      "&diff&" 
     group by toDate(Col1) 
     format "&diff&" 'h.m'"))
    输出：
    迄今为止（） 商（差（最大最小值）86400000（））
    2024 年 1 月 4 日 2.15
    2024 年 1 月 5 日 3.0
    • 0

相关问题