表 WSHOSHO

SELECT * FROM WSHOSHO;

建议零售价表

SELECT * FROM RRP;

加入说明问题

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

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

结果：

IE：

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-SQL 代码，用于设置此处使用的示例数据。

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);

上面的代码是在 SQL Server 的 T-SQL 中。但是，我对任何被视为“惯用 SQL”的 SQL 语言解决方案持开放态度。

PowerShell 方法

我有一种方法适用于 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
    }
}

白猫头鹰的方法

这是一种基于下面 White Owl 的回答的方法。

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);

我有一个类型列表：

SELECT * FROM type;

id          name
----------- --------------------------------------------------
1           person
2           other god
3           location
4           role
5           gender

以及一个对象列表，每个对象都有一个类型：

SELECT * FROM object;

id          name                                               type_id
----------- -------------------------------------------------- -----------
1           Adam                                               1
2           Eve                                                1
3           Cain                                               1
4           Abel                                               1
5           Jeroboam                                           1
6           Zeredah                                            3

以及显示类型名称的视图：

SELECT * FROM object_view;

id          name                                               type_name
----------- -------------------------------------------------- --------------------------------------------------
1           Adam                                               person
2           Eve                                                person
3           Cain                                               person
4           Abel                                               person
5           Jeroboam                                           person
6           Zeredah                                            location

关系类型列表：

SELECT * FROM relationship;

id          name
----------- --------------------------------------------------
1           has father
2           has mother
3           from

以及对象之间的关系列表：

SELECT * FROM object_relationship;

object_a_id relationship_id object_b_id
----------- --------------- -----------
4           1               1
3           2               2
5           3               6

以及对这些关系的看法：

SELECT * FROM object_relationship_view;

object_a                                           relationship                                       object_b
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
Abel                                               has father                                         Adam
Cain                                               has mother                                         Eve
Jeroboam                                           from                                               Zeredah

我想用 、 和 列列出father每个mother对象from。如果对象不具有这些属性之一，则该列应显示NULL. 所以结果应该是这样的：

这是一种似乎可行的方法：

SELECT      id,

            object.name,

            (
                SELECT      (SELECT name FROM object WHERE id = object_b_id)
                FROM        object_relationship 
                WHERE       object.id = object_relationship.object_a_id AND 
                            object_relationship.relationship_id = (SELECT id FROM relationship WHERE name = 'has father')
            ) AS father,

            (
                SELECT      (SELECT name FROM object WHERE id = object_b_id)
                FROM        object_relationship 
                WHERE       object.id = object_relationship.object_a_id AND 
                            object_relationship.relationship_id = (SELECT id FROM relationship WHERE name = 'has mother')
            ) AS mother,

            (
                SELECT      (SELECT name FROM object WHERE id = object_b_id)
                FROM        object_relationship 
                WHERE       object.id = object_relationship.object_a_id AND 
                            object_relationship.relationship_id = (SELECT id FROM relationship WHERE name = 'from')
            ) AS [from]

FROM        object;

我的问题是：这可以通过 JOIN 来完成吗？

这种方法很接近：

SELECT      object.name,
            (SELECT name FROM object WHERE id = REL_FATHER.object_b_id) AS father,
            (SELECT name FROM object WHERE id = REL_MOTHER.object_b_id) AS mother,
            (SELECT name FROM object WHERE id = REL_FROM.object_b_id)   AS [from]

FROM        object
            LEFT JOIN   object_relationship AS REL_FATHER   ON  object.id = REL_FATHER.object_a_id
            LEFT JOIN   object_relationship AS REL_MOTHER   ON  object.id = REL_MOTHER.object_a_id
            LEFT JOIN   object_relationship AS REL_FROM     ON  object.id = REL_FROM.object_a_id

WHERE       REL_FATHER.relationship_id = (SELECT id FROM relationship WHERE name = 'has father')    AND
            REL_MOTHER.relationship_id = (SELECT id FROM relationship WHERE name = 'has mother')    AND
            REL_FROM.relationship_id   = (SELECT id FROM relationship WHERE name = 'from');

这种方法的问题在于它只列出了具有和值father的对象。如果其中任何一个为 `NULL，则不会列出它们。motherfrom

因此，例如，如果您有以下添加的关系数据：

EXEC insert_object_relationship 'Abel', 'has father', 'Adam';
EXEC insert_object_relationship 'Abel', 'has mother', 'Eve';
EXEC insert_object_relationship 'Abel', 'from',       'Eden';

EXEC insert_object_relationship 'Cain', 'has father', 'Adam';
EXEC insert_object_relationship 'Cain', 'has mother', 'Eve';
EXEC insert_object_relationship 'Cain', 'from',       'Eden';

EXEC insert_object_relationship 'Jeroboam', 'from', 'Zeredah';

上面的查询返回以下内容：

（请注意未列出，因为该条目与和Zeredah没有关系。fathermother

有没有比上面显示的方法更好的方法？

我确信上面描述的技术不是新的；欢迎任何指向讨论此问题的参考文献的指针。（即在数据库理论文本中有这个名称吗？）

生成这些表和数据所需的所有代码都包含在下面。

如果你觉得这个问题更适合stackoverflow，请告诉我，我会在那里问。

感谢您的任何建议！

DROP TABLE IF EXISTS object_relationship;
DROP TABLE IF EXISTS object;
--------------------------------------------------------------------------------
DROP TABLE IF EXISTS type;

CREATE TABLE type
(
    id      INT             NOT NULL    PRIMARY KEY     IDENTITY(1, 1),
    name    nvarchar(50)    NOT NULL
);
--------------------------------------------------------------------------------
CREATE TABLE object
(
    id      INT             NOT NULL    PRIMARY KEY     IDENTITY(1, 1),
    name    nvarchar(50)    NOT NULL,
    type_id int             NOT NULL    CONSTRAINT FK_object_type FOREIGN KEY REFERENCES type(id)
);
--------------------------------------------------------------------------------
DROP TABLE IF EXISTS relationship;

CREATE TABLE relationship
(
    id      INT             NOT NULL    PRIMARY KEY     IDENTITY(1, 1),
    name    nvarchar(50)    NOT NULL
);
--------------------------------------------------------------------------------
CREATE TABLE object_relationship
(
    object_a_id     INT                 CONSTRAINT FK_object_relationship_object_object_a   FOREIGN KEY REFERENCES object(id),
    relationship_id INT                 CONSTRAINT FK_object_relationship_relationship      FOREIGN KEY REFERENCES relationship(id),
    object_b_id     INT                 CONSTRAINT FK_object_relationship_object_object_b   FOREIGN KEY REFERENCES object(id)
);
--------------------------------------------------------------------------------
DROP VIEW IF EXISTS object_view;
GO

CREATE VIEW object_view

AS

SELECT  object.id, object.name AS name, type.name AS type_name
FROM    object INNER JOIN type ON object.type_id = type.id;

GO
--------------------------------------------------------------------------------
DROP VIEW IF EXISTS object_relationship_view;
GO

CREATE VIEW object_relationship_view

AS

SELECT      A.name AS object_a, relationship.name AS relationship, B.name AS object_b
FROM        object AS A INNER JOIN object_relationship ON A.id = object_relationship.object_a_id
                        INNER JOIN relationship ON object_relationship.relationship_id = relationship.id
                        INNER JOIN object AS B ON B.id = object_relationship.object_b_id;
GO
--------------------------------------------------------------------------------
INSERT INTO type (name)
VALUES
('person'),
('other god'),
('location'),
('role'),
('gender');
DROP PROC IF EXISTS insert_object;      

GO

CREATE PROC insert_object
    @object     AS nvarchar(50),
    @type       AS nvarchar(50)

AS

INSERT INTO object (name, type_id)
VALUES
(@object, (SELECT id FROM type WHERE name = @type));

GO
--------------------------------------------------------------------------------
EXEC insert_object 'Adam',      'person';
EXEC insert_object 'Eve',       'person';
EXEC insert_object 'Cain',      'person';
EXEC insert_object 'Abel',      'person';
EXEC insert_object 'Jeroboam',  'person';
EXEC insert_object 'Zeredah',   'location';
EXEC insert_object 'Eden',  'location';
--------------------------------------------------------------------------------
INSERT INTO relationship (name)
VALUES
('has father'),
('has mother'),
('from');
--------------------------------------------------------------------------------
DROP PROC IF EXISTS insert_object_relationship;
GO

CREATE PROC insert_object_relationship
    @a  AS nvarchar(50),
    @relationship AS nvarchar(50),
    @b AS nvarchar(50)

AS

INSERT INTO object_relationship (object_a_id, relationship_id, object_b_id)
VALUES
((SELECT id FROM object WHERE name = @a), (SELECT id FROM relationship WHERE name = @relationship), (SELECT id FROM object WHERE name = @b));

GO
--------------------------------------------------------------------------------
EXEC insert_object_relationship 'Abel', 'has father', 'Adam';
EXEC insert_object_relationship 'Cain', 'has mother', 'Eve';
EXEC insert_object_relationship 'Jeroboam', 'from', 'Zeredah';