时间戳

首页 > 解决方案 > 组合/折叠行与 group_by + 以列为下一行的条件

sql - 组合/折叠行与 group_by + 以列为下一行的条件

问题描述

我所拥有的是一家电信公司与新客户签约的聊天记录。在聊天中，客户和公司代表进行了聊天。

我正在尝试折叠聊天，以减少行数。下图显示了之前数据的外观以及之后数据的外观。

前

现在的数据集

后

我想看到什么

我看过以下文章：

我试过这段代码：

select 
    unique_id, string_agg(concat(text, ' ', text), ', ')
from 
    conversation
group by 
    unique_id, user

但是，这不会根据需要折叠它。它将其完全折叠为 2 行，一条用于客户，另一条用于公司。我正在寻找的逻辑是如果此查询中的下一行包含相同的 unique_id，则用户将当前行文本字段与下一行文本字段连接起来。

这是 SQL Fiddle 页面，但我在 SQL Server 中运行此代码string_agg：http ://sqlfiddle.com/#!9/5ad86c/3

如果您查看我的 StackOverflow 历史记录，我已经在 R 中请求了一个几乎类似的算法。

CREATE TABLE conversation
(
     `unique_id` double, 
     `line_no` int, 
     `user` varchar(7000), 
     `text` varchar(7000)
);

INSERT INTO conversation (`unique_id`, `line_no`, `user`, `text`)
VALUES
    (50314585222, 1, 'customer', 'Hi I would like to sign up for a service'),
    (50314585222, 2, 'company', 'Hi My name is Alex. We can offer the following plans. We also have signup bonuses, with doubling of data for 12 months '),
    (50314585222, 3, 'company', 'Plan1: 40GB data, with monthly price of $80'),
    (50314585222, 4, 'company', 'Plan2: 20GB data, with monthly price of $40'),
    (50314585222, 5, 'company', 'Plan3: 5GB data, with monthly price of $15'),
    (50314585222, 6, 'customer', 'I was hoping for a much smaller plan, with only voice service'),
    (50314585222, 7, 'customer', 'maybe the $10 per month plan.'),
    (50319875222, 4, 'customer', 'so how do I sign up'),
    (50319875222, 5, 'customer', '*for the service'),
    (50319875222, 7, 'company', 'maybe I can call you for your details?')
;

标签： sqlsql-servertsqlrelational-databasegaps-and-islands

解决方案

如果我对您的理解正确，则下一种方法是一种可能的解决方案。您需要找到更改并定义适当的组：

桌子：

CREATE TABLE [conversation]
(
     [unique_id] bigint, 
     [line_no] int, 
     [user] varchar(7000), 
     [text] varchar(7000)
);

INSERT INTO [conversation] ([unique_id], [line_no], [user], [text])
VALUES
    (50314585222, 1, 'customer', 'Hi I would like to sign up for a service'),
    (50314585222, 2, 'company', 'Hi My name is Alex. We can offer the following plans. We also have signup bonuses, with doubling of data for 12 months '),
    (50314585222, 3, 'company', 'Plan1: 40GB data, with monthly price of $80'),
    (50314585222, 4, 'company', 'Plan2: 20GB data, with monthly price of $40'),
    (50314585222, 5, 'company', 'Plan3: 5GB data, with monthly price of $15'),
    (50314585222, 6, 'customer', 'I was hoping for a much smaller plan, with only voice service'),
    (50314585222, 7, 'customer', 'maybe the $10 per month plan.'),
    (50319875222, 4, 'customer', 'so how do I sign up'),
    (50319875222, 5, 'customer', '*for the service'),
    (50319875222, 7, 'company', 'maybe I can call you for your details?')
;

陈述：

; WITH ChangesCTE AS (
    SELECT 
        *,
        LAG([user]) OVER (PARTITION BY [unique_id] ORDER BY [line_no]) AS prev_user
    FROM [conversation]
), GroupsCTE AS (
    SELECT 
        *,
        SUM(CASE WHEN [user] <> [prev_user] OR [prev_user] IS NULL THEN 1 ELSE 0 END) OVER (PARTITION BY [unique_id] ORDER BY [line_no]) AS [group_id]
    FROM ChangesCTE
)
SELECT 
    [unique_id], 
    MIN([line_no]) AS [line_no], 
        MIN([user]) AS [user], 
        STRING_AGG([text], ' ') WITHIN GROUP (ORDER BY [line_no]) AS [text]
FROM GroupsCTE
GROUP BY [unique_id], [group_id]
ORDER BY [unique_id]

结果：

unique_id   line_no user        text
50314585222 1       customer    Hi I would like to sign up for a service
50314585222 2       company     Hi My name is Alex. We can offer the following plans. We also have signup bonuses, with doubling of data for 12 months  Plan1: 40GB data, with monthly price of $80 Plan2: 20GB data, with monthly price of $40 Plan3: 5GB data, with monthly price of $15
50314585222 6       customer    I was hoping for a much smaller plan, with only voice service maybe the $10 per month plan.
50319875222 4       customer    so how do I sign up *for the service
50319875222 7       company     maybe I can call you for your details?

推荐阅读