用Sybase中的另一个类似选项替换lag()函数

Question

在stackoverflow的某些用户的帮助下，我访问了以下查询。问题在于此查询在SQL Server中运行良好，但在SYBASE中不支持lag（）。我一直在努力使它工作，但没有成功。

查询非常复杂，但有助于用类似的东西来实现滞后（）功能。

SELECT created
    , bname
    , total_share
FROM (
    SELECT TT.created
        , TT.bname
        , TT.total_share
        , lag(TT.total_share) OVER (
            PARTITION BY TT.bname ORDER BY TT.created
            ) AS prev_share
    FROM (
        SELECT DISTINCT t.created AS created
            , t.NAME AS bname
            , t.total_share
        FROM (
            SELECT cast(fsp.created AS VARCHAR(19)) AS created
                , e.NAME
                , e.initials
                , fsp.modified
                , CASE 
                    WHEN cl.price_per_item = 0
                        THEN CAST('' AS DECIMAL(18, 2))
                    ELSE CAST((fsp.new_price / cl.price_per_item * 100) AS DECIMAL(18, 2))
                    END AS new_share
                , CASE 
                    WHEN cl.price_per_item = 0
                        THEN CAST('' AS DECIMAL(18, 2))
                    ELSE CAST((prev / cl.price_per_item * 100) AS DECIMAL(18, 2))
                    END AS old_share
                , fs.STATE
                , CASE 
                    WHEN fsp.prev_price IS NULL
                        THEN 0
                    ELSE fsp.prev_price
                    END AS prev
                , fsp.new_price AS nprice
                , (prev - nprice) AS diff
                , new_share - old_share AS diff_share
                , old_share + diff_share AS total_share
            FROM project_manager pm
            INNER JOIN dba.project p ON pm.project = p.id
            LEFT JOIN dba.contract c ON p.id = c.project
            LEFT JOIN dba.contract_line cl ON cl.contract = c.id
            LEFT JOIN dba.product pt ON cl.product = pt.id
            LEFT JOIN dba.specified_product sp ON sp.product = pt.id
            LEFT JOIN dba.frozen_sale fs ON fs.spec_product = sp.id
                AND fs.contract = c.id
                AND fs.line = cl.idx
            LEFT JOIN dba.frozen_sale_split fsp ON fsp.frozen_sale = fs.id
                AND fsp.employee = pm.consultant
            LEFT JOIN dba.employee e ON fsp.employee = e.person
            LEFT JOIN dba.person ps ON fsp.creator = ps.id
            WHERE p.id = 50000002735
                AND e.NAME IS NOT NULL
            ) AS t
        ) TT /* here */
    ) x
WHERE (
        prev_share IS NULL
        OR prev_share  total_share
        )
ORDER BY created
    , bname

Answer 1

未经测试的代码，但是方法是使用row_number窗口函数将顺序编号（代码中的 rnum）分配给每个行，由 created value顺序排序。这是在初始CTE中完成的。然后，主要查询将CTE加入本身，并在rnum值中具有差异的连接条件。

 with inp as (
    SELECT TT.created
        , TT.bname
        , TT.total_share
        , row_number over(order by TT.created) as rnum
    FROM (
        SELECT DISTINCT t.created AS created
            , t.NAME AS bname
            , t.total_share
        FROM (
            SELECT cast(fsp.created AS VARCHAR(19)) AS created
                , e.NAME
                , e.initials
                , fsp.modified
                , CASE 
                    WHEN cl.price_per_item = 0
                        THEN CAST('' AS DECIMAL(18, 2))
                    ELSE CAST((fsp.new_price / cl.price_per_item * 100) AS DECIMAL(18, 2))
                    END AS new_share
                , CASE 
                    WHEN cl.price_per_item = 0
                        THEN CAST('' AS DECIMAL(18, 2))
                    ELSE CAST((prev / cl.price_per_item * 100) AS DECIMAL(18, 2))
                    END AS old_share
                , fs.STATE
                , CASE 
                    WHEN fsp.prev_price IS NULL
                        THEN 0
                    ELSE fsp.prev_price
                    END AS prev
                , fsp.new_price AS nprice
                , (prev - nprice) AS diff
                , new_share - old_share AS diff_share
                , old_share + diff_share AS total_share
            FROM project_manager pm
            INNER JOIN dba.project p ON pm.project = p.id
            LEFT JOIN dba.contract c ON p.id = c.project
            LEFT JOIN dba.contract_line cl ON cl.contract = c.id
            LEFT JOIN dba.product pt ON cl.product = pt.id
            LEFT JOIN dba.specified_product sp ON sp.product = pt.id
            LEFT JOIN dba.frozen_sale fs ON fs.spec_product = sp.id
                AND fs.contract = c.id
                AND fs.line = cl.idx
            LEFT JOIN dba.frozen_sale_split fsp ON fsp.frozen_sale = fs.id
                AND fsp.employee = pm.consultant
            LEFT JOIN dba.employee e ON fsp.employee = e.person
            LEFT JOIN dba.person ps ON fsp.creator = ps.id
            WHERE p.id = 50000002735
                AND e.NAME IS NOT NULL
            ) AS t
        ) TT
    ) 
SELECT
    created
    , bname
    , total_share
    , prev.total_share as prev_share
FROM
    inp
    left join inp as prev on prev.rnum=inp.rnum-1
        and prev.bname=inp.bname
WHERE (
    prev.total_share IS NULL
    OR prev.total_share  total_share
    )
ORDER BY 
    created
    , bname