使用Python基于从多个其他表中的条件选择从一个SQLite表中进行选择

rdrgkggo  于 2023-01-13  发布在  SQLite
关注(0)|答案(1)|浏览(114)

我尝试从一个SQLite "股票行情"表中选择4H时间范围内的行情（来自时间范围表），其最后三个（所有三个，而不仅仅是其中一个）收盘价高于相同价格柱的9移动平均线...和一个一维时间框架（来自时间范围表），其最后三个（所有三个，而不仅仅是其中一个）收盘价高于相同价格线的9移动平均线......还有一个是1W时间框架（来自时间范围表），其最后三个（所有三个，而不仅仅是其中一个）收盘价高于相同价格线的9移动平均线......还有一个是1M时间框架（来自时间框架表），其最近三个（所有三个，而不仅仅是其中一个）收盘价都高于同一价格条的9均线。
所有时间条上的所有条件都必须满足，除非时间范围表没有足够的数据或具有NaN值，则条件可以在三个或两个甚至一个时间范围上运行。
我的5张表如下
1.股票行情表

cursor.execute("""
    CREATE TABLE IF NOT EXISTS stocks_list (
        id INTEGER PRIMARY KEY,
        symbol TEXT NOT NULL UNIQUE,
        name TEXT NOT NULL,
        exchange TEXT NOT NULL,
        connection TEXT NOT NULL
    )
""")

数据如下：

INSERT INTO stocks_list (1,FEXD,Fintech Ecosystem Development Corp. Class A Common Stock,NASDAQ,Alpaca Markets);
INSERT INTO stocks_list (2,FEXDR,Fintech Ecosystem Development Corp. Right,NASDAQ,Alpaca Markets);
INSERT INTO stocks_list (3,FEXDU,Fintech Ecosystem Development Corp. Units,NASDAQ,Alpaca Markets);
INSERT INTO stocks_list (4,FGF,"FG Financial Group, Inc. Common Stock",NASDAQ,Alpaca Markets);
INSERT INTO stocks_list (5,FGI,FGI Industries Ltd. Ordinary Shares,NASDAQ,Alpaca Markets);
INSERT INTO stocks_list (6,FGLD,Franklin Responsibly Sourced Gold ETF,ARCA,Alpaca Markets);
INSERT INTO stocks_list (7,FLGC,Flora Growth Corp. Common Stock,NASDAQ,Alpaca Markets);
INSERT INTO stocks_list (8,FLGR,Franklin FTSE Germany ETF,ARCA,Alpaca Markets);
  1. 4H价格及指标值表
cursor.execute("""
    CREATE TABLE IF NOT EXISTS indicators_4H (
        price_id INTEGER,
        stock_id INTEGER,
        date NOT NULL,
        open NOT NULL,
        high NOT NULL,
        low NOT NULL,
        close NOT NULL,
        volume NOT NULL,
        sma9 REAL,
        sma20 REAL,
        CONSTRAINT stock_prices_4H_fk FOREIGN KEY (price_id) REFERENCES stock_prices_4H (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
        CONSTRAINT stocks_list_fk FOREIGN KEY (stock_id) REFERENCES stocks_list (id)
        ON DELETE CASCADE
    )
""")

数据如下：

INSERT INTO indicators_4H (188,1,2022-12-08T11:00:00,10.2,10.2,10.2,10.2,988,10.142222222222223,10.104);
INSERT INTO indicators_4H (187,1,2022-12-01T07:00:00,10.14,10.14,10.14,10.14,2312,10.12888888888889,10.097);
INSERT INTO indicators_4H (186,1,2022-11-22T15:00:00,10.15,10.15,10.15,10.15,119200,10.124444444444444,10.093);
INSERT INTO indicators_4H (185,1,2022-11-22T11:00:00,10.14,10.15,10.14,10.15,213768,10.115555555555556,10.0885);
INSERT INTO indicators_4H (184,1,2022-11-21T15:00:00,10.14,10.14,10.14,10.14,1500,10.106666666666667,10.084);
INSERT INTO indicators_4H (183,1,2022-11-21T11:00:00,10.14,10.15,10.1398,10.15,5025,10.100000000000001,10.08);
INSERT INTO indicators_4H (182,1,2022-11-18T11:00:00,10.12,10.13,10.12,10.13,500,10.091111111111111,10.0755);
INSERT INTO indicators_4H (346,2,2022-12-09T15:00:00,0.15,0.15,0.15,0.15,16726,0.1634111111111111,0.15446,0.1299456);
INSERT INTO indicators_4H (345,2,2022-12-08T11:00:00,0.1517,0.1517,0.15,0.15,240,0.1620222222222222,0.15471000000000001,0.1289456);
INSERT INTO indicators_4H (344,2,2022-11-17T11:00:00,0.1703,0.1713,0.17,0.17,10000,0.16013333333333332,0.153215,0.12793759999999998);
INSERT INTO indicators_4H (343,2,2022-10-21T08:00:00,0.1875,0.1875,0.1875,0.1875,300,0.1579111111111111,0.152565,0.1265376);
INSERT INTO indicators_4H (342,2,2022-10-20T12:00:00,0.18,0.193,0.1797,0.193,122200,0.15374444444444443,0.151055,0.12458759999999999);
INSERT INTO indicators_4H (341,2,2022-10-20T08:00:00,0.155,0.1801,0.155,0.1801,890870,0.14985555555555555,0.14940499999999998,0.1225336);
INSERT INTO indicators_4H (420,3,2022-09-26T12:00:00,10.1,10.13,10.1,10.13,421,10.13111111111111,10.13938);
INSERT INTO indicators_4H (419,3,2022-08-22T12:00:00,10.13,10.13,10.1,10.1,400,10.127777777777778,10.143379999999999);
INSERT INTO indicators_4H (418,3,2022-08-19T12:00:00,10.13,10.13,10.1,10.1,400,10.128888888888888,10.14688);
INSERT INTO indicators_4H (417,3,2022-08-11T12:00:00,10.11,10.11,10.11,10.11,100,10.13111111111111,10.15213);
INSERT INTO indicators_4H (416,3,2022-07-29T12:00:00,10.14,10.14,10.14,10.14,850,10.132222222222222,10.158130000000002);
INSERT INTO indicators_4H (415,3,2022-07-27T12:00:00,10.13,10.13,10.13,10.13,100,10.128888888888888,10.163129999999999);
  1. 1D价格和指标值表
cursor.execute("""
    CREATE TABLE IF NOT EXISTS indicators_1D (
        price_id INTEGER,
        stock_id INTEGER,
        date NOT NULL,
        open NOT NULL,
        high NOT NULL,
        low NOT NULL,
        close NOT NULL,
        volume NOT NULL,
        sma9 REAL,
        sma20 REAL,
        CONSTRAINT stock_prices_4H_fk FOREIGN KEY (price_id) REFERENCES stock_prices_4H (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
        CONSTRAINT stocks_list_fk FOREIGN KEY (stock_id) REFERENCES stocks_list (id)
        ON DELETE CASCADE
    )
""")

数据如下：

INSERT INTO indicators_1D (229,1,2022-12-09,10.2,10.2,10.2,10.2,0,10.155555555555557,10.1425);
INSERT INTO indicators_1D (228,1,2022-12-08,10.2,10.2,10.2,10.2,1006,10.15,10.136500000000002);
INSERT INTO indicators_1D (227,1,2022-12-07,10.14,10.14,10.14,10.14,0,10.144444444444444,10.130500000000001);
INSERT INTO indicators_1D (226,1,2022-12-06,10.14,10.14,10.14,10.14,0,10.145555555555555,10.127500000000001);
INSERT INTO indicators_1D (225,1,2022-12-05,10.14,10.14,10.14,10.14,0,10.146666666666668,10.124500000000001);
INSERT INTO indicators_1D (224,1,2022-12-02,10.14,10.14,10.14,10.14,0,10.146666666666668,10.121500000000001);
INSERT INTO indicators_1D (458,2,2022-12-09,0.15,0.15,0.15,0.15,16731,0.16555555555555557,0.1715);
INSERT INTO indicators_1D (457,2,2022-12-08,0.1517,0.1517,0.15,0.15,265,0.1677777777777778,0.173375);
INSERT INTO indicators_1D (456,2,2022-12-07,0.17,0.17,0.17,0.17,0,0.17,0.17525000000000002);
INSERT INTO indicators_1D (455,2,2022-12-06,0.17,0.17,0.17,0.17,0,0.17,0.17612500000000003);
INSERT INTO indicators_1D (454,2,2022-12-05,0.17,0.17,0.17,0.17,0,0.17,0.17700000000000002);
INSERT INTO indicators_1D (453,2,2022-12-02,0.17,0.17,0.17,0.17,0,0.17,0.177875);
INSERT INTO indicators_1D (747,3,2022-12-09,10.13,10.13,10.13,10.13,0,10.13,10.13);
INSERT INTO indicators_1D (746,3,2022-12-08,10.13,10.13,10.13,10.13,0,10.13,10.13);
INSERT INTO indicators_1D (745,3,2022-12-07,10.13,10.13,10.13,10.13,0,10.13,10.13);
INSERT INTO indicators_1D (744,3,2022-12-06,10.13,10.13,10.13,10.13,0,10.13,10.13);
INSERT INTO indicators_1D (743,3,2022-12-05,10.13,10.13,10.13,10.13,0,10.13,10.13);
INSERT INTO indicators_1D (742,3,2022-12-02,10.13,10.13,10.13,10.13,0,10.13,10.13);
  1. 1W价格及指标值表
cursor.execute("""
    CREATE TABLE IF NOT EXISTS indicators_1W (
        price_id INTEGER,
        stock_id INTEGER,
        date NOT NULL,
        open NOT NULL,
        high NOT NULL,
        low NOT NULL,
        close NOT NULL,
        volume NOT NULL,
        sma9 REAL,
        sma20 REAL,
        CONSTRAINT stock_prices_4H_fk FOREIGN KEY (price_id) REFERENCES stock_prices_4H (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
        CONSTRAINT stocks_list_fk FOREIGN KEY (stock_id) REFERENCES stocks_list (id)
        ON DELETE CASCADE
    )
""")

数据如下：

INSERT INTO indicators_1W (48,1,2022-12-05,10.14,10.2,10.14,10.2,1006,10.108888888888888,10.086250000000001);
INSERT INTO indicators_1W (47,1,2022-11-28,10.15,10.15,10.14,10.14,2322,10.094444444444445,10.07675);
INSERT INTO indicators_1W (46,1,2022-11-21,10.14,10.15,10.1398,10.15,339645,10.085555555555556,10.07025);
INSERT INTO indicators_1W (45,1,2022-11-14,10.08,10.14,10.0701,10.13,2301,10.075555555555557,10.06275);
INSERT INTO indicators_1W (44,1,2022-11-07,10.08,10.08,10.08,10.08,0,10.065555555555555,10.055250000000001);
INSERT INTO indicators_1W (43,1,2022-10-31,10.07,10.1,10.07,10.08,46495,10.071111111111113,10.05025);
INSERT INTO indicators_1W (96,2,2022-12-05,0.17,0.17,0.15,0.15,16996,0.17194444444444443,0.135965);
INSERT INTO indicators_1W (95,2,2022-11-28,0.17,0.17,0.17,0.17,0,0.17055555555555557,0.13371499999999997);
INSERT INTO indicators_1W (94,2,2022-11-21,0.17,0.17,0.17,0.17,0,0.16644444444444448,0.129715);
INSERT INTO indicators_1W (93,2,2022-11-14,0.1875,0.1875,0.17,0.17,10000,0.16422222222222221,0.12621500000000002);
INSERT INTO indicators_1W (92,2,2022-11-07,0.1875,0.1875,0.1875,0.1875,0,0.1586777777777778,0.12132000000000001);
INSERT INTO indicators_1W (91,2,2022-10-31,0.1875,0.1875,0.1875,0.1875,0,0.14545555555555556,0.116205);
INSERT INTO indicators_1W (156,3,2022-12-05,10.13,10.13,10.13,10.13,0,10.13,10.120999999999999);
INSERT INTO indicators_1W (155,3,2022-11-28,10.13,10.13,10.13,10.13,0,10.13,10.119499999999999);
INSERT INTO indicators_1W (154,3,2022-11-21,10.13,10.13,10.13,10.13,0,10.13,10.118);
INSERT INTO indicators_1W (153,3,2022-11-14,10.13,10.13,10.13,10.13,0,10.126666666666669,10.1165);
INSERT INTO indicators_1W (152,3,2022-11-07,10.13,10.13,10.13,10.13,0,10.123333333333333,10.115);
INSERT INTO indicators_1W (151,3,2022-10-31,10.13,10.13,10.13,10.13,0,10.120000000000001,10.113499999999998);
INSERT INTO indicators_1W (150,3,2022-10-24,10.13,10.13,10.13,10.13,0,10.116666666666667,10.1125);
  1. 1M价格及指标值表
cursor.execute("""
    CREATE TABLE IF NOT EXISTS indicators_1M (
        price_id INTEGER,
        stock_id INTEGER,
        date NOT NULL,
        open NOT NULL,
        high NOT NULL,
        low NOT NULL,
        close NOT NULL,
        volume NOT NULL,
        sma9 REAL,
        sma20 REAL,
        CONSTRAINT stock_prices_4H_fk FOREIGN KEY (price_id) REFERENCES stock_prices_4H (id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
        CONSTRAINT stocks_list_fk FOREIGN KEY (stock_id) REFERENCES stocks_list (id)
        ON DELETE CASCADE
    )
""")

数据如下：

INSERT INTO indicators_1M (12,1,2022-12-01,10.14,10.2,10.14,10.2,3328,10.072000000000001);
INSERT INTO indicators_1M (11,1,2022-11-01,10.07,10.15,10.07,10.15,388441,10.043111111111111);
INSERT INTO indicators_1M (10,1,2022-10-01,10.05,10.09,10.05,10.07,107123,10.016444444444444);
INSERT INTO indicators_1M (9,1,2022-09-01,10.13,10.13,10.04,10.06,96270,9.996444444444444);
INSERT INTO indicators_1M (8,1,2022-08-01,10.06,10.2,10.03,10.13,39199,);
INSERT INTO indicators_1M (7,1,2022-07-01,9.98,10.25,9.98,10.06,2056887,);
INSERT INTO indicators_1M (24,2,2022-12-01,0.17,0.17,0.15,0.15,16996,0.13384444444444446);
INSERT INTO indicators_1M (23,2,2022-11-01,0.1875,0.1875,0.17,0.17,10000,0.13603333333333334);
INSERT INTO indicators_1M (22,2,2022-10-01,0.133,0.193,0.133,0.1875,1552669,0.1315888888888889);
INSERT INTO indicators_1M (21,2,2022-09-01,0.0803,0.2502,0.0685,0.133,1222028,0.1318777777777778);
INSERT INTO indicators_1M (20,2,2022-08-01,0.0823,0.105,0.075,0.0803,61524,);
INSERT INTO indicators_1M (39,3,2022-12-01,10.13,10.13,10.13,10.13,0,10.133333333333333);
INSERT INTO indicators_1M (38,3,2022-11-01,10.13,10.13,10.13,10.13,0,10.14);
INSERT INTO indicators_1M (37,3,2022-10-01,10.13,10.13,10.13,10.13,0,10.137777777777778);
INSERT INTO indicators_1M (36,3,2022-09-01,10.1,10.13,10.1,10.13,463,10.14);
INSERT INTO indicators_1M (35,3,2022-08-01,10.14,10.14,10.1,10.1,1040,10.147777777777776);
INSERT INTO indicators_1M (34,3,2022-07-01,10.1,10.23,10.1,10.14,1755,10.161111111111111);
INSERT INTO indicators_1M (33,3,2022-06-01,10.12,10.14,10.1,10.14,21305,10.172222222222222);

如何编写SQLite查询使其工作？

sqlite

1条答案

按热度按时间
46qrfjad

46qrfjad1#

您可以简单地使用临时表，如下所示;

# Create the temporary table
cursor.execute("""
    CREATE TEMPORARY TABLE temp (stock_id INTEGER);
    """)

# get all stock_id that meet conditions on 4H table
cursor.execute("""
    INSERT INTO temp (stock_id)
    SELECT stock_id FROM indicators_4H
    WHERE date >= (SELECT date FROM indicators_4H ORDER BY date DESC LIMIT 3 OFFSET 0)
    AND indicators_4H.close > indicators_4H.sma9 
    """)
# get all stock_id that meet conditions on 1D table
cursor.execute("""
    INSERT INTO temp (stock_id)
    SELECT stock_id FROM indicators_1D
    WHERE date >= (SELECT date FROM indicators_1D ORDER BY date DESC LIMIT 3 OFFSET 0)
    AND indicators_1D.close > indicators_1D.sma9 
    """)

# get all stock_id that meet conditions on 1W table
cursor.execute("""
    INSERT INTO temp (stock_id)
    SELECT stock_id FROM indicators_1W
    WHERE date >= (SELECT date FROM indicators_1W ORDER BY date DESC LIMIT 3 OFFSET 0)
    AND indicators_1W.close > indicators_1W.sma9
    """)

# get all stock_id that meet conditions on 1M table
cursor.execute("""
    INSERT INTO temp (stock_id)
    SELECT stock_id FROM indicators_1M
    WHERE date >= (SELECT date FROM indicators_1M ORDER BY date DESC LIMIT 3 OFFSET 0)
    AND indicators_1M.close > indicators_1M.sma9
    """)

# Now you need to filter the temp table so only stocks that meet conditions from all four tables
# will be considered
cursor.execute("""
    CREATE TEMPORARY TABLE temp2 AS 
    SELECT stock_id FROM temp GROUP BY stock_id HAVING COUNT(*) = 4 
    """)

# Execute the final query
cursor.execute("""
    SELECT DISTINCT stocks_list.symbol, stocks_list.name
    FROM stocks_list JOIN temp2 ON stocks_list.id = temp2.stock_id
    """)

这将返回所有四个表中收盘价高于sma 9的股票

赞(0）分享  回复(0）举报  2023-01-13
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com