时间戳

首页 > 解决方案 > 带除法的sqlite嵌套查询

sql - 带除法的sqlite嵌套查询

问题描述

我对 SQL 有点陌生，我想知道最好的方法来做到这一点。基本上一个查询返回分母，而外部查询需要以百分比形式返回分子/分母。每个语句基本上使用相同的表。

create table games(
    id integer NOT NULL,
    name TEXT NOT NULL,
    category   TEXT NOT NULL
);

create table game_sets(
    id integer NOT NULL,
    name TEXT NOT NULL,
    theme_id  integer NOT NULL
);

INSERT INTO games (id, name, category)
VALUES (1, "star wars", "top game"),
(2, "pokemon", "top game"),
(3, "zelda", "top game"),
(4, "crazy cats", "sucky game");

INSERT INTO game_sets(id, name, theme_id)
VALUES (1, "star wars set 1", 1),
(2, "star wars set 2", 1),
(3, "star wars set 3", 1),
(4, "pikachu set 1", 2),
(5, "narf set 1", 4),
(6, "narf set 2", 4),
(7, "narf set 1", 4),
(8, "narf set 1", 4),
(9, "narf set 1", 4),
(10, "narf set 1", 4);



CREATE VIEW top_games AS
SELECT id, name
FROM games
WHERE category ='top game';

--i hard coded 200 below, but it needs to be dynamic

select top_games.name as theme, printf("%.2f", cast(count(game_sets.name)as float)/200) as num_sets_percent from top_games
join game_sets
where top_games.id = game_sets.theme_id
group by top_games.id
order by num_sets desc
limit 2;

--below here is the number i need for the first query to divide
--i have it hard coded as 4 b/c 4 total sets in the game_sets table, but it needs to be dynamic with this query

(select count(game_sets.name) as num_sets from game_sets
join top_games
where top_games.id = game_sets.theme_id) as divide_by_this

输出：星球大战，0.3（因为总共 10 组中有 3 组星球大战，而星球大战是顶级游戏）口袋妖怪，0.1（因为总共 10 组中有 1 组口袋妖怪也是顶级游戏）最后我们限制了它只有 2 顶集，所以 zelda 集不会出现。

标签： sqlsqlitenested-query

解决方案

如果你有 SQLite 3.25.0+，你可以使用窗口函数：

select distinct
  g.name,
  1.0 * count(g.id) over (partition by g.id) / count() over () num_sets_percent
from game_sets s left join top_games g
on s.theme_id = g.id
order by num_sets_percent desc
limit 2

请参阅演示。
结果：

| name      | num_sets_percent |
| --------- | ---------------- |
| star wars | 0.3              |
| pokemon   | 0.1              |

推荐阅读