SQL按字符串的一部分进行分组

Question

我在我的表格中有以下数据:

URL         TIME    DATE
--------------------------------------
/x          11      2013-08-01
/x          11      2013-08-01
/pl/        11      2013-08-01
/pl/        11      2013-08-03
/pl/XXX/    11      2013-08-01
/pl/XXX/    11      2013-08-04
/pl/XXX/1   11      2013-08-01
/pl/XXX/2   11      2013-08-01
/pl/YYY/    11      2013-08-01
/pl/YYY/1   11      2013-08-01
/pl/YYY/2   11      2013-08-04
/pl/YYY/3   11      2013-08-04

在SQL Server中，是否有一种方法可以按URL的前三个斜杠（/）进行分组？不幸的是，存在小于三个斜杠的记录。

- Piotr Stapp

---

你应该阅读这个问题：https://dev59.com/R2oy5IYBdhLWcg3wKrCs - MarcinJuraszek

这是一条、两条还是三条斜杠：“pl/XXX”？我问这个问题是因为它可能和“/pl/XXX/”是相同的路径。 - Tim Schmelter

@TimSchmelter 所有数据都以斜杠开头，所以这不是问题。 - Piotr Stapp

Answer 1

计算字符串中斜杠数量的一个技巧是：

len(url) - len(replace(url,'/',''))

您可以使用charindex三次来查找第三个斜杠的位置：

select  BeforeThirdSlash
,       max([date])
from    (
        select  case 
                when len(url) - len(replace(url,'/','')) < 3 then url
                else substring(url, 1, charindex('/', url, charindex('/', 
                         url, charindex('/', url)+1)+1)-1)
                end as BeforeThirdSlash
        ,       *
        from    @t
        ) as SubQueryAlias
group by
        BeforeThirdSlash

SQL Fiddle上的实时示例。

- Andomar

Answer 2

一个简单的表达式，可以让您获取到第三个'/'字符之前的子字符串，如下所示：

case
    when patindex('%/%/%/%', url) = 0 then url
    else left(url,charindex('/',url,charindex('/',url,charindex('/',url)+1)+1))
end

patindex函数检查是否有至少三个斜杠；left函数提取包括第三个斜杠在内的子字符串。

使用这个表达式，编写group by语句就变得简单了。

SELECT
    url3, max(tm), max(dt)
FROM (
    SELECT
        CASE
            WHEN patindex('%/%/%/%', url) = 0 THEN url
            ELSE left(url,charindex('/',url,charindex('/',url,charindex('/',url)+1)+1))
        END AS url3
    ,   tm
    ,   dt
    FROM test
) x
GROUP BY url3

这是在SqlFiddle上的演示。

- Sergey Kalinichenko

Answer 3

您可以找到每个/的位置并修剪到MAX(position) - 假设第三个/是最后一个/，根据数据。

DECLARE @tbl TABLE ( u VARCHAR(255), t INT, d DATE)

INSERT INTO @tbl (u, t, d) VALUES
('/x',          11,      '2013-08-01'),
('/x',          11,      '2013-08-01'),
('/pl/',        11,      '2013-08-01'),
('/pl/',        11,      '2013-08-03'),
('/pl/XXX/',    11,      '2013-08-01'),
('/pl/XXX/',    11,      '2013-08-04'),
('/pl/XXX/1',   11,      '2013-08-01'),
('/pl/XXX/2',   11,      '2013-08-01'),
('/pl/YYY/',    11,      '2013-08-01'),
('/pl/YYY/1',   11,      '2013-08-01'),
('/pl/YYY/2',   11,      '2013-08-04'),
('/pl/YYY/3',   11,      '2013-08-04')

;WITH split AS (
    SELECT u, 1 s, CHARINDEX('/', u) p
    FROM @tbl
    UNION ALL
    SELECT u, p + 1, CHARINDEX('/', u, p + 1)
    FROM split
)

SELECT LEFT(t.u, split.i), MAX(t.t), MAX(t.d)
FROM @tbl t
JOIN (
    SELECT u, MAX(p) i 
    FROM split
    GROUP BY u
) split ON split.u = t.u
GROUP BY LEFT(t.u, split.i)

通过对cte进行微调，您可以控制发生的情况。

DECLARE @n INT = 3    -- 'nth occurence'

;WITH split AS (
    SELECT u, CHARINDEX('/', u) i, 1 r
    FROM (
        SELECT DISTINCT u
        FROM @tbl
    ) t
    WHERE CHARINDEX('/', u) > 0
    UNION ALL
    SELECT u, CHARINDEX('/', u, i + 1), r + 1
    FROM split
    WHERE r < @n
    AND CHARINDEX('/', u, i + 1) > 0
)

SELECT LEFT(t.u, split.i) u, MAX(t.t) t , MAX(t.d) d
FROM @tbl t
JOIN split ON split.u = t.u
GROUP BY LEFT(t.u, split.i)

sql fiddle demo

- T I