文章/答案/技术大牛

发布

社区首页 >问答首页 >联接并使用最新的数据(如果为NULL )

问联接并使用最新的数据(如果为NULL )
EN

Database Administration用户

提问于 2023-04-08 19:10:19

回答 2查看 61关注 0票数 0

表WSHOSHO

SELECT * FROM WSHOSHO;

表RRP

SELECT * FROM RRP;

联接以说明问题

这个联接的结果可以用来说明这个问题：

SELECT * FROM WSHOSHO RIGHT JOIN RRP ON (WSHOSHO.DATE = RRP.DATE);

结果：

即：

For any given row of RRP
    If there's a `WSHOSHO` row with the same date, use that

    Otherwise, use the most recent WSHOSHO before RRP.DATE

问题

实现这一目标的好查询是什么？

示例数据

这里有一些T代码来设置这里使用的示例数据。

DROP TABLE WSHOSHO;

DROP TABLE RRP;

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='WSHOSHO')
    CREATE TABLE WSHOSHO (
        DATE    varchar(255), 
        WSHOSHO decimal
    )

IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='RRP')
    CREATE TABLE RRP (
        DATE    varchar(255), 
        RRP decimal
    )

INSERT INTO WSHOSHO
VALUES 
('2023-01-11', 8100175.0), 
('2023-01-18', 8079010.0), 
('2023-01-25', 8062665.0), 
('2023-02-01', 8024470.0), 
('2023-02-08', 8024104.0), 
('2023-02-15', 7990467.0), 
('2023-02-22', 7986887.0), 
('2023-03-01', 7948534.0), 
('2023-03-08', 7948335.0), 
('2023-03-15', 7940014.0), 
('2023-03-22', 7936558.0), 
('2023-03-29', 7926131.0), 
('2023-04-05', 7877114.0);

INSERT INTO RRP
VALUES
('2023-01-03', 2188272000000), 
('2023-01-04', 2229542000000), 
('2023-01-05', 2242486000000), 
('2023-01-06', 2208265000000), 
('2023-01-09', 2199121000000), 
('2023-01-10', 2192942000000), 
('2023-01-11', 2199170000000), 
('2023-01-12', 2202989000000), 
('2023-01-13', 2179781000000), 
('2023-01-17', 2093328000000), 
('2023-01-18', 2131678000000), 
('2023-01-19', 2110145000000), 
('2023-01-20', 2090523000000), 
('2023-01-23', 2135499000000), 
('2023-01-24', 2048386000000), 
('2023-01-25', 2031561000000), 
('2023-01-26', 2024069000000), 
('2023-01-27', 2003634000000), 
('2023-01-30', 2048714000000), 
('2023-01-31', 2061572000000), 
('2023-02-01', 2038262000000), 
('2023-02-02', 2050063000000), 
('2023-02-03', 2041217000000), 
('2023-02-06', 2072261000000), 
('2023-02-07', 2057958000000), 
('2023-02-08', 2059604000000), 
('2023-02-09', 2058942000000), 
('2023-02-10', 2042893000000), 
('2023-02-13', 2107775000000), 
('2023-02-14', 2076548000000), 
('2023-02-15', 2011998000000), 
('2023-02-16', 2032457000000), 
('2023-02-17', 2059662000000), 
('2023-02-21', 2046064000000), 
('2023-02-22', 2113849000000), 
('2023-02-23', 2147417000000), 
('2023-02-24', 2142141000000), 
('2023-02-27', 2162435000000), 
('2023-02-28', 2188035000000), 
('2023-03-01', 2133950000000), 
('2023-03-02', 2192355000000), 
('2023-03-03', 2186150000000), 
('2023-03-06', 2190793000000), 
('2023-03-07', 2170195000000), 
('2023-03-08', 2193237000000), 
('2023-03-09', 2229623000000), 
('2023-03-10', 2188375000000), 
('2023-03-13', 2126677000000), 
('2023-03-14', 2042579000000), 
('2023-03-15', 2055823000000), 
('2023-03-16', 2066319000000), 
('2023-03-17', 2106166000000), 
('2023-03-20', 2098393000000), 
('2023-03-21', 2194631000000), 
('2023-03-22', 2279608000000), 
('2023-03-23', 2233956000000), 
('2023-03-24', 2218458000000), 
('2023-03-27', 2220131000000), 
('2023-03-28', 2231749000000), 
('2023-03-29', 2264862000000), 
('2023-03-30', 2271531000000), 
('2023-03-31', 2375171000000), 
('2023-04-03', 2221010000000), 
('2023-04-04', 2219375000000), 
('2023-04-05', 2243011000000), 
('2023-04-06', 2173663000000);

上述代码为Server的T格式.但是，我对任何被认为是“惯用SQL”的SQL语言解决方案都持开放态度。

PowerShell approach

我有一种对PowerShell中的数据有效的方法，如下所示。

但是，我想知道如何使用惯用的SQL。

$wshosho_data = Invoke-RestMethod 'https://fred.stlouisfed.org/graph/fredgraph.csv?id=WSHOSHO' | ConvertFrom-Csv
$rrp_data     = Invoke-RestMethod ('https://markets.newyorkfed.org/api/rp/reverserepo/propositions/search.json?startDate={0}' -f '2022-04-08')
# ----------------------------------------------------------------------
$wshosho_sorted = $wshosho_data             | Sort-Object DATE
$rrp_sorted     = $rrp_data.repo.operations | Sort-Object operationDate
# ----------------------------------------------------------------------
$wshosho_sorted | Select-Object -Last 10 | ft *
$rrp_sorted     | Select-Object -Last 10 | ft operationDate, totalAmtAccepted
# ----------------------------------------------------------------------
$wshosho_dates = $wshosho_sorted | ForEach-Object DATE
$rrp_dates     = $rrp_sorted     | ForEach-Object operationDate
# ----------------------------------------------------------------------
$rrp_earliest     = $rrp_dates     | Sort-Object | Select-Object -First 1
$wshosho_earliest = $wshosho_dates | Sort-Object | Select-Object -First 1

$earliest = $rrp_earliest, $wshosho_earliest | Sort-Object | Select-Object -Last 1
# ----------------------------------------------------------------------
$dates = $rrp_dates + $wshosho_dates | Sort-Object | Select-Object -Unique | Where-Object { $_ -GE $earliest }
# ----------------------------------------------------------------------
$table = foreach ($date in $dates)
{
    $rrp_record     = $rrp_sorted.Where(     { $_.operationDate -le $date }, 'Last' )[0]
    $wshosho_record = $wshosho_sorted.Where( { $_.DATE          -le $date }, 'Last' )[0]

    $rrp_item     = [decimal] $rrp_record.totalAmtAccepted
    $wshosho_item = [decimal] $wshosho_record.WSHOSHO

    [PSCustomObject]@{
        date    = $date
        wshosho = $wshosho_item
        rrp     = $rrp_item
    }
}

白猫头鹰的进场

下面是一种基于白猫头鹰的回答的方法。

SELECT
RRP.DATE,
(
    CASE WHEN WSHOSHO.DATE IS NULL
        THEN
        (
            SELECT TOP(1) TBL.WSHOSHO FROM WSHOSHO as TBL WHERE TBL.DATE < RRP.DATE ORDER BY TBL.DATE DESC
        )
        ELSE
            WSHOSHO.WSHOSHO
        END
) AS WSHOSHO,
RRP.RRP
FROM RRP LEFT JOIN WSHOSHO ON (WSHOSHO.DATE = RRP.DATE);

join

database-agnostic

回答 2

Database Administration用户

回答已采纳

发布于 2023-04-08 21:22:28

这样的任务在命令式语言(perl、python等)中更容易完成。几乎所有的报告工具都有这样的功能。

在SQL中，这确实是很难做到的，但是很少有窍门。

如果只需要填充一个字段，则可以在列中进行子选择。

select
   rrp.date,
   rrp.rrp,
   (case when wshosho.date is null 
        then (select b.wshosho
              from wshosho b
              where b.date

Server使用TOP或OFFSET/FETCH。

如果DBMS支持临时表，那么

select date, 0 as wshosho into #t from rrp
update #t set wshosho = wshosho from wshosho where #t.date=wshosho.date
update #t set wshosho = (select wshosho from #t b where b.date<#t.date order by desc limit 1)

select * from rrp join #t on rrp.date = #t.date

这也可以通过循环中的游标来完成(如果DBMS有这样的能力)。

但通常情况下，这是在客户端用顺序获取完成的。

票数 0

Database Administration用户

发布于 2023-04-09 07:17:47

在Server 2022中，惯用解决方案将使用窗口函数：

SELECT
    R.[DATE],
    WSHOSHO =
        LAST_VALUE(W.WSHOSHO) IGNORE NULLS OVER (
            ORDER BY R.[DATE] ASC
            ROWS UNBOUNDED PRECEDING),
    R.RRP
FROM WSHOSHO AS W
RIGHT JOIN RRP AS R
    ON (R.[DATE] = W.[DATE])
ORDER BY
    R.[DATE] ASC;

db<>fiddle演示

密切相关的问答：如何在大型表的有序列中获得最后一个非空值？

票数 3

页面原文内容由Database Administration提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://dba.stackexchange.com/questions/325791

复制

相似问题

问联接并使用最新的数据(如果为NULL )
EN

表WSHOSHO

表RRP

联接以说明问题

问题

示例数据

PowerShell approach

白猫头鹰的进场

回答 2

Database Administration用户

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问联接并使用最新的数据(如果为NULL )EN

表WSHOSHO

表RRP

联接以说明问题

问题

示例数据

PowerShell approach

白猫头鹰的进场

回答 2

Database Administration用户

Database Administration用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问联接并使用最新的数据(如果为NULL )
EN