CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Smart003
Smart003

Reputation: 1119

Query which will perform better than recursive cte

I have a data around 5 million records, the following is the sample data which will relates to it

if object_id('tempdb..#Table1') is not null
drop TABLE #Table1
CREATE TABLE #Table1
    ([Hierarchy_No] varchar(8), [sales] int)
;

INSERT INTO #Table1
    ([Hierarchy_No], [sales])
VALUES
    ('1-1.1.1.', 100),
    ('1-1.1.2.', 200),
    ('1-1.2.1', 300),
    ('1-1.2.1', 400),
    ('1-2.1.1.', 500),
    ('1-2.1.2.', 600),
    ('1-2.2.1', 700),
    ('1-2.2.1', 800)
;

we have used recursive cte to achieve the following result.

Hierarchy_No    sales
1-1.            1000
1-2.            2600
1-1.1.          300
1-1.2.          700
1-2.1.          1100
1-2.2.          1500
1-1.1.1.        100
1-1.1.2.        200
1-1.2.1         300
1-1.2.1         400
1-2.1.1.        500
1-2.1.2.        600
1-2.2.1.        700
1-2.2.1.        800

To achieve results for 1-1. we have to add sales of 1-1.1.1.+1-1.1.2.+1-1.2.1.+1-1-2.1. i.e is 1000. is there any way to achieve the results other than recursive cte? Kindly help.

Upvotes: 2

Views: 225

Answers (3)

cloudsafe
cloudsafe

Reputation: 2506

This solutions turns each hierarchy_no into rows of every character to get the required hierarchies:

select x.HierarchyCheck as [Hierarchy_No], sum(t1.sales) as [sales]
from #table1 t1
inner join (
    SELECT  distinct     IIF(SUBSTRING([Hierarchy_No],Number,1) = '.', LEFT([Hierarchy_No], number), '') as HierarchyCheck
    FROM #Table1
    CROSS APPLY (SELECT DISTINCT number FROM master..spt_values WHERE number > 0 AND number <= LEN([Hierarchy_No]))V ) as x on t1.Hierarchy_No like x.HierarchyCheck + '%'  and right(x.HierarchyCheck, 1) ='.'
group by x.HierarchyCheck
UNION
select T1.Hierarchy_No as [Hierarchy_No], t1.sales as [sales]
from #table1 t1
WHERE RIGHT(T1.Hierarchy_No,1)<>'.'

Upvotes: 0

uzi
uzi

Reputation: 4146

Here's one way using hierarchyid data type

select
    stuff(replace(replace(res.GetAncestor(n).ToString(), '.', '-'), '/', '.'), 1, 1, '')
    , sum(sales)
from (
    select
        *, res = cast('/' + replace(replace(Hierarchy_No, '.', '/'), '-', '.') as hierarchyid)

    from
        #Table1 c
        join (values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) t(n) 
            on len(Hierarchy_No) - len(replace(Hierarchy_No, '.', '')) > t.n
) t
group by res.GetAncestor(n).ToString()

Notes:

  1. Hierarchy_No must be numeric as in your example. Only one non numeric character can be handled by converting it to .
  2. There's no other column that identifies that last two Hierarchy_No are different. So they are grouped in the output. List it in group by statement if you have such a column

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82504

Here is one way to do it (still using a recursive cte, but since you didn't share yours, I have no idea if that's going to be a better one):

;WITH RCTE AS
(
    SELECT  [Hierarchy_No],
            CHARINDEX('.', [Hierarchy_No]) As DotPosition,
            [sales]
    FROM  #Table1
    UNION ALL

    SELECT  [Hierarchy_No],
            CHARINDEX('.', [Hierarchy_No], DotPosition + 1),
            [sales]
    FROM RCTE
    WHERE DotPosition > 0 AND DotPosition < LEN([Hierarchy_No]) - 1
)

SELECT LEFT([Hierarchy_No], DotPosition) As Hierarchy, SUM([sales]) As Total_Sales
FROM RCTE 
GROUP BY LEFT([Hierarchy_No], DotPosition)

I've also tried using a numbers table instead of a recursive cte, but all my attempts where proven less effective for this sample data.

SELECT  LEFT ([Hierarchy_No], Number) As Hierarchy,
        SUM(sales)
FROM  #Table1
INNER JOIN 
(
    SELECT Number 
    FROM Tally 
    WHERE Number <= 8 -- (the maximum length of the `[Hierarchy_No]` column)
)
Tally ON SUBSTRING([Hierarchy_No], Number, 1) = '.'
GROUP BY LEFT ([Hierarchy_No], Number) 
ORDER BY Hierarchy

Upvotes: 2

Related Questions