尝试从第1、第2、第3、第4（等等）以前的日期返回数据到列中

huangapple
go评论85阅读模式
英文:

Trying to return data into columns from 1st, 2nd, 3rd, 4th (etc.) previous dates

问题

Table 1

+--------+------------+-------+
|  URI   |    Date    | Name  |
+--------+------------+-------+
| Fred4  | 2023-04-05 | Fred  |
| Fred3  | 2023-04-01 | Fred  |
| Fred2  | 2023-03-15 | Fred  |
| Fred1  | 2023-03-06 | Fred  |
| Dave3  | 2023-05-22 | Dave  |
| Dave2  | 2023-05-11 | Dave  |
| Dave1  | 2023-05-03 | Dave  |
| Simon6 | 2023-05-20 | Simon |
| Simon5 | 2023-05-11 | Simon |
| Simon4 | 2023-04-21 | Simon |
| Simon3 | 2023-04-19 | Simon |
| Simon2 | 2023-04-12 | Simon |
| Simon1 | 2023-03-25 | Simon |
+--------+------------+-------+

Table 2

+--------+------------+--------+
|  URI   |    Date    |  Item  |
+--------+------------+--------+
| Fred4  | 2023-04-05 | Top    |
| Fred3  | 2023-04-01 | Shorts |
| Fred2  | 2023-03-15 | Band   |
| Fred1  | 2023-03-06 | Top    |
| Dave3  | 2023-05-22 | Shorts |
| Dave2  | 2023-05-11 | Shoes  |
| Dave1  | 2023-05-03 | Top    |
| Simon6 | 2023-05-20 | Shorts |
| Simon5 | 2023-05-11 | Band   |
| Simon4 | 2023-04-21 | Shorts |
| Simon3 | 2023-04-19 | Top    |
| Simon2 | 2023-04-12 | Shoes  |
| Simon1 | 2023-03-25 | Shoes  |
+--------+------------+--------+

For each URI, I am trying to pull in each previous item so that Item1 = last bought, Item2 = 2nd last bought, etc:

+--------+------------+--------+--------+-------+-------+-------+
|  URI   |    Date    | Item1  | Item2  | Item3 | Item4 | Item5 |
+--------+------------+--------+--------+-------+-------+-------+
| Fred4  | 2023-04-05 | Shorts | Band   | Top   |       |       |
| Fred3  | 2023-04-01 | Band   | Top    |       |       |       |
| Fred2  | 2023-03-15 | Top    |        |       |       |       |
| Fred1  | 2023-03-06 | NULL   |        |       |       |       |
| Dave3  | 2023-05-22 | Shoes  | Top    |       |       |       |
| Dave2  | 2023-05-11 | Top    |        |       |       |       |
| Dave1  | 2023-05-03 | NULL   |        |       |       |       |
| Simon6 | 2023-05-20 | Band   | Shorts | Top   | Shoes | Shoes |
| Simon5 | 2023-05-11 | Shorts | Top    | Shoes | Shoes |       |
| Simon4 | 2023-04-21 | Top    | Shoes  | Shoes |       |       |
| Simon3 | 2023-04-19 | Shoes  | Shoes  |       |       |       |
| Simon2 | 2023-04-12 | Shoes  |        |       |       |       |
| Simon1 | 2023-03-25 | NULL   |        |       |       |       |
+--------+------------+--------+--------+-------+-------+-------+

Currently, I can get the most recent last item, but I am struggling with the 2nd, 3rd, etc. despite trying a few different things.

WITH ranked_items AS (
    SELECT 
        t1.URI, 
        t1.Date, 
        t1.Name,
        t2.Item,
        ROW_NUMBER() OVER (PARTITION BY t1.URI ORDER BY t2.Date DESC) AS rn
    FROM Table1 t1
    LEFT JOIN Table2 t2 ON t1.Name = t2.Name AND t1.Date > t2.Date
)
SELECT 
    r.URI, 
    r.Date, 
    r.Name,
    MAX(CASE WHEN r.rn = 1 THEN r.Item ELSE NULL END) AS Item1,
    MAX(CASE WHEN r.rn = 2 THEN r.Item ELSE NULL END) AS Item2,
    MAX(CASE WHEN r.rn = 3 THEN r.Item ELSE NULL END) AS Item3,
    MAX(CASE WHEN r.rn = 4 THEN r.Item ELSE NULL END) AS Item4,
    MAX(CASE WHEN r.rn = 5 THEN r.Item ELSE NULL END) AS Item5
FROM ranked_items r
GROUP BY r.URI, r.Date, r.Name
ORDER BY r.URI, r.Date, r.Name;

This SQL query should give you the desired result by using the ROW_NUMBER() function to rank the items based on the purchase date and then selecting the items for each rank (Item1, Item2, etc.) using conditional aggregation.

英文:

Table 1

+--------+------------+-------+
|  URI   |    Date    | Name  |
+--------+------------+-------+
| Fred4  | 2023-04-05 | Fred  |
| Fred3  | 2023-04-01 | Fred  |
| Fred2  | 2023-03-15 | Fred  |
| Fred1  | 2023-03-06 | Fred  |
| Dave3  | 2023-05-22 | Dave  |
| Dave2  | 2023-05-11 | Dave  |
| Dave1  | 2023-05-03 | Dave  |
| Simon6 | 2023-05-20 | Simon |
| Simon5 | 2023-05-11 | Simon |
| Simon4 | 2023-04-21 | Simon |
| Simon3 | 2023-04-19 | Simon |
| Simon2 | 2023-04-12 | Simon |
| Simon1 | 2023-03-25 | Simon |
+--------+------------+-------+

Table 2

+--------+------------+--------+
|  URI   |    Date    |  Item  |
+--------+------------+--------+
| Fred4  | 2023-04-05 | Top    |
| Fred3  | 2023-04-01 | Shorts |
| Fred2  | 2023-03-15 | Band   |
| Fred1  | 2023-03-06 | Top    |
| Dave3  | 2023-05-22 | Shorts |
| Dave2  | 2023-05-11 | Shoes  |
| Dave1  | 2023-05-03 | Top    |
| Simon6 | 2023-05-20 | Shorts |
| Simon5 | 2023-05-11 | Band   |
| Simon4 | 2023-04-21 | Shorts |
| Simon3 | 2023-04-19 | Top    |
| Simon2 | 2023-04-12 | Shoes  |
| Simon1 | 2023-03-25 | Shoes  |
+--------+------------+--------+

For each URI I am trying to pull in each previous item so that Item1 = last bought, item2 = 2nd last bought etc:

+--------+------------+--------+--------+-------+-------+-------+
|  URI   |    Date    | Item1  | Item2  | Item3 | Item4 | Item5 |
+--------+------------+--------+--------+-------+-------+-------+
| Fred4  | 2023-04-05 | Shorts | Band   | Top   |       |       |
| Fred3  | 2023-04-01 | Band   | Top    |       |       |       |
| Fred2  | 2023-03-15 | Top    |        |       |       |       |
| Fred1  | 2023-03-06 | NULL   |        |       |       |       |
| Dave3  | 2023-05-22 | Shoes  | Top    |       |       |       |
| Dave2  | 2023-05-11 | Top    |        |       |       |       |
| Dave1  | 2023-05-03 | NULL   |        |       |       |       |
| Simon6 | 2023-05-20 | Band   | Shorts | Top   | Shoes | Shoes |
| Simon5 | 2023-05-11 | Shorts | Top    | Shoes | Shoes |       |
| Simon4 | 2023-04-21 | Top    | Shoes  | Shoes |       |       |
| Simon3 | 2023-04-19 | Shoes  | Shoes  |       |       |       |
| Simon2 | 2023-04-12 | Shoes  |        |       |       |       |
| Simon1 | 2023-03-25 | NULL   |        |       |       |       |
+--------+------------+--------+--------+-------+-------+-------+

Currently I can get the most recent last item but I am struggling with the 2nd, 3rd etc. despite trying a few different things.

with all_data as (
    SELECT t1.URI, t1.date, t1.name
    ,t2.Item as Item1, t2.Item as Item2, t2.Item as Item3, t2.Item as Item4, t2.Item as Item5
    FROM Table1 t1
    LEFT OUTER JOIN Table2 t2 on t1.name = t2.name and t1.date > t2.date
)
SELECT URI, date, name, Item1, Item2, Item3, Item4, Item5
from all_data
group by URI, date, name
order by URI, date, name
;

Any help would be great 尝试从第1、第2、第3、第4（等等）以前的日期返回数据到列中 I'm pretty sure the query needs to be quite a lot bigger unless someone knows a few tricks.

Using MySQL and HeidiSQL client.

答案1

得分: 1

以下是翻译好的部分：

with t as (
  select t1.uri, t1.date, t1.name, t2.Item,
           row_number() over (partition by t1.name order by t2.Date desc ) rn
  from Table1 t1 join Table2 t2 on t1.URI = t2.URI and t1.date >= t2.date),
joined as (
  select a.uri, a.date, a.name, b.item, b.rn - a.rn rn 
  from t a left join t b on a.name = b.name and a.rn < b.rn )
select uri, date, 
       max(case rn when 1 then item end) item1,
       max(case rn when 2 then item end) item2,
       max(case rn when 3 then item end) item3,
       max(case rn when 4 then item end) item4,
       max(case rn when 5 then item end) item5
from joined group by uri, date, name order by name, date desc

dbfiddle演示

步骤：

  • 对子查询中的行进行编号，
  • 基于名称和a.rn < b.rn执行自连接，这会生成包括所需的空行（Simon1，Fred1）的条目，
  • 通过减去行号a.rn - b.rn，得到适用于旋转操作的正确值，
  • 进行经典的旋转操作，
  • 按未显示的列名和日期降序排序行，如果您想这样做。

您在奇怪的旋转类别中获得了奖项；-)

英文: 
with t as (
  select  t1.uri, t1.date, t1.name, t2.Item,
           row_number() over (partition by t1.name order by t2.Date desc ) rn
  from Table1 t1 join Table2 t2 on t1.URI = t2.URI and t1.date &gt;= t2.date),
joined as (
  select a.uri, a.date, a.name, b.item, b.rn - a.rn rn 
  from t a left join t b on a.name = b.name and a.rn &lt; b.rn )
select uri, date, 
       max(case rn when 1 then item end) item1,
       max(case rn when 2 then item end) item2,
       max(case rn when 3 then item end) item3,
       max(case rn when 4 then item end) item4,
       max(case rn when 5 then item end) item5
from joined group by uri, date, name order by name, date desc

dbfiddle demo

Steps:

  • number the rows in the subquery,
  • perform a self-join of this subquery based on name and a.rn < b.rn, which multiplies entries including desired empty rows (Simon1, Fred1),
  • subtract row numbers a.rn - b.rn, this gives correct value for pivot to operate,
  • make classic pivot,
  • sort rows by undisplayed column name and date desc, if you want it this way.

You win an award in the category of strange pivots 尝试从第1、第2、第3、第4（等等）以前的日期返回数据到列中

huangapple
  • 本文由 发表于 2023年6月22日 01:27:35
  • 转载请务必保留本文链接：https://go.coder-hub.com/76525805.html
  • greatest-n-per-group
  • join
  • mysql
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定