首页 / 技术问答 / Mysql问答列表 / 问答详情

Mysql 如何重新采样SQL数据库

我有一个数据集，看起来像下面的:

ITEM    CITY        START_Y   START_W   FIRST_USE_Y   FIRST_USE_W   VALUE
A       NEW YORK    2023      30             2023             32    15000
A       LONDON      2024       2             2024              2    12000
A       LONDON      2024       2             2024              5    50000
B       NEW YORK    2023      49             2024              1    19540
B       MADRID      2023      10             2023             11    15444

首先，项目和城市的组合需要分组.然后，对于每个组，我希望每周重新采样最多5个数据点，并在FIRST_USE_Y和FIRST_USE_W列组合没有值的情况下用零填充'PRODUCT'列. START_W和FIRST_USE_W是一年的周数(值可以从1到52).

我试过pandas和for循环，效果很好.但由于它是一个非常大的数据集，有数百万行，我一定会使用SQL(我是一个新手).这是我试过的代码:

WITH RECURSIVE weekly_intervals AS (
    SELECT MIN(start_w) AS start_w, MAX(start_w) AS end_w
    FROM citywise_values
    UNION ALL
    SELECT start_w + INTERVAL 1 WEEK, end_w
    FROM weekly_intervals
    WHERE start_w + INTERVAL 1 WEEK <= end_w
),
filled_values AS (
    SELECT 
        w.item,
        w.city,
        w.start_y,
        w.start_w,
        COALESCE(cv.value, 0) AS value
    FROM 
        (SELECT 
            item,
            city,
            start_y,
            start_w
        FROM 
            citywise_values
        GROUP BY 
            item, city) w
    LEFT JOIN 
        citywise_values cv ON w.item = cv.item
                             AND w.city = cv.city
                             AND w.start_y = cv.start_y
                             AND w.start_w = cv.start_w
)
SELECT 
    item,
    city,
    start_y,
    start_w,
    COALESCE(value, LAG(value) OVER (PARTITION BY item, city, start_y ORDER BY start_w)) AS value
FROM 
    filled_values
RIGHT JOIN
    weekly_intervals
ON
    filled_values.start_w = weekly_intervals.start_w
ORDER BY
    item, city, start_y, start_w

然后，我try 了一个交叉连接，并能够产生的结果，只有一个单一的项目和城市组合.但我无法找到如何处理整个数据集.

我不确定我能解释得好还是不好.因此，我发布了我手动创建的所需输出.

ITEM    CITY        START_Y     START_W     FIRST_USE_Y     FIRST_USE_W     VALUE
A       NEW YORK    2023        30                2023              30      0
A       NEW YORK    2023        30                2023              31      0
A       NEW YORK    2023        30                2023              32      15000
A       NEW YORK    2023        30                2023              33      0
A       NEW YORK    2023        30                2023              34      0
A       LONDON      2024        2                 2024              2       12000
A       LONDON      2024        2                 2024              3       0
A       LONDON      2024        2                 2024              4       0
A       LONDON      2024        2                 2024              5       50000
A       LONDON      2024        2                 2024              6       0
B       NEW YORK    2023        49                2023              49      0
B       NEW YORK    2023        49                2023              50      0
B       NEW YORK    2023        49                2023              51      0
B       NEW YORK    2023        49                2023              52      0
B       NEW YORK    2023        49                2024              1       19540
B       MADRID      2023        10                2023              10      0
B       MADRID      2023        10                2023              11      15444
B       MADRID      2023        10                2023              12      0
B       MADRID      2023        10                2023              13      0
B       MADRID      2023        10                2023              14      0

任何帮助将不胜感激.

推荐答案

这里有三个项目使这个查询变得棘手:

  1. 投影到5行(我使用了表值构造函数，但还有其他选项，包括SQL Server 2022中的generate_series()行或递归CTE)
  2. 在一年的时候处理.关键是日期是messy.Avoid doing date math work yourself.相反，尽可能依赖于平台内置的日期功能.在这种情况下，这意味着将年/周值转换为实际日期.这也是我推荐用这种方式存放东西的原因之一.您可以将这些值存储为日期，实际日期是该周的周日值.
  3. 尽管有#2，我仍然不得不对手动日期计算做出一个让步:由于年份并不总是在同一天开始，数据中的周数似乎并不总是匹配SQL Server返回的"regular"或iso_week，可能是由于样本数据的错误，我将周数视为自1月1日以来的7天块手动处理.

我也看到了这个:

START_W和FIRST_USE_W是一年的周数(值可以从1到52).

There are more than 52 weeks in a year!

每年将有一个部分星期53，至少有一天或两天.你必须能解释这件事.

我想出了这个，甚至使用了一个交叉连接:

WITH ItemCity As (
    SELECT Item, City, MIN(  DATEADD(day, Start_W*7, DATEFROMPARTS(Start_Y, 1, 1)) ) As StartWeek
    FROM Data
    GROUP BY Item, City
), 
ItemCityWeeks As (
   SELECT Item,City, StartWeek
       ,Year(StartWeek) As Start_Y,datepart(week, StartWeek)-1 As Start_W
       ,YEAR(DATEADD(day, Weeks.num*7, StartWeek)) As First_Use_Y
       ,DATEPART(dayofyear, DATEADD(day, Weeks.num*7, StartWeek))/7 As First_Use_W
   FROM ItemCity
   CROSS JOIN ( VALUES (0), (1), (2), (3), (4)) Weeks(num)
)
SELECT icw.Item, icw.City
      , icw.Start_Y, icw.Start_W, icw.First_Use_Y, icw.First_Use_W
      , coalesce(d.value, 0) as Value
FROM ItemCityWeeks icw
LEFT JOIN Data d ON d.Item = icw.Item AND d.City = icw.City 
      and d.First_Use_Y = icw.First_Use_Y and d.First_Use_W = icw.First_Use_W
ORDER BY Item, City DESC

在这里看到它的工作:

https://dbfiddle.uk/1PyTujMX

还请注意，我的第一次使用周是由第一个城市的一个休息.我相信这是手动创建的样本结果中的一个错误，因为其他城市都以same week作为Start_W开始，而这一个城市在一周后开始.

Mysql相关问答推荐

MySQL多次联接同一个表使计数变得奇怪

最终的自联表是如何记住关联的呢？

如何从mysql中的不可用日期范围获取可用日期范围？

如何进行查询以在两个不同的列中搜索两个不同的数据字符串？

关于设置为 NOT NULL 时的 CHAR 默认值

Mysql如何分行分词

使用sql查找源和最终目的地

插入二进制数据会导致Data too long for column...

提取 MySQL 5.7 连续值的差异

如何替换 SELECT 查询中重复记录的列？

按 SQL 删除分组前的重复项

动态创建内联 SQL 表(用于排除左连接)

PHP，MySQL 错误：列计数与第 1 行的值计数不匹配

如何存储百分比值？

MySQL - 使一对值唯一

我如何决定何时使用右连接/左连接或内连接或者如何确定哪个表在哪一侧？

在 MySQL 中检测 utf8 损坏的字符

在mysql中复制没有数据的数据库 struct (带有空表)

带有 LIKE 运算符的 Select 语句中的 MySQL case

如果另一列为空，则 Select 一列

实用课程推荐
相关教程推荐
休闲君评测 |  TOOLFK工具网 |  古诗文网 |  良许Linux教程网 |  Python之禅 |  红色石头 |  入党申请书 | 

© 2020 Hi LearnFk 意见反馈 免责声明 关于我们 桂ICP备11002319号-3 友链&广告位+QQ: 1963612630