在PostgreSQL中找到每个由Y组成的X的比例？ [英] Find the proportion of each X consisting of Y in PostgreSQL?

问题描述

我有一个很大的Magic数据库：Gathering卡和卡片组。纸牌表包含每张纸牌的类型和转换的法术力费用（除其他事项外）。甲板使用两个表存储：甲板本身的表称为甲板，以及称为甲板成员的表，其中每一行包含甲板的ID，该甲板中包含的卡的ID以及

I have a big database of Magic: the Gathering cards and decklists. The table of cards contains the type and converted mana cost of each card (among other things). The decks are stored using two tables: a table of the decks themselves called "decks", and a table called "deckmembers", in which each row contains the ID of a deck, the ID of a card contained in that deck, and the number of copies of that card that appear in the deck.

我想要的是该数据的视图，其中的行是：

What I want is a view of this data in which the rows are:

牌组：牌组的ID

cmc：该牌组中至少一张卡片上出现的转换后的法术力费用

cmc: a converted mana cost appearing on at least one card in that deck

比例：该套牌中具有cmc的非陆地牌的百分比

proportion: the percentage of the nonland cards in that deck that have that cmc

或者我最好用Python或R还是什么？

Or am I better off deriving this data in Python or R or something?

这个问题在概念上相似，但是没有人回答。

编辑：

自您提出问题后，下面是一些示例数据：

Since you asked, here's some example data:

卡：

 id |        name         |    fulltype     | cmc
----+---------------------+-----------------+-----
  1 | "Ach! Hans, Run!"   | Enchantment     |   6
  2 | 1996 World Champion | Summon _ Legend |   5
  4 | AWOL                | Instant         |   3
  5 | Abandon Hope        | Sorcery         |   2
  6 | Abandon Reason      | Instant         |   3

甲板：

 id |      name
----+-----------------
  1 | RDW
  2 | Red Deck Recall
  3 | RDW
  4 | Red Deck Wins
  5 | Red Deck Wins

deckmembers：

deckmembers:

 deck | card  | count
------+-------+-------
    1 | 14031 |     1
    1 | 15011 |     1
    1 | 14263 |     1
    1 | 12966 |     1
    1 | 12536 |     1

任何套牌都会有很多卡。任何卡都可能出现在许多卡组中。每张卡都有一个从0到12的整数与之相关联，称为转换后的法术力费用或CMC。这就是您所需要知道的。

Any deck will have many cards. Any card may appear in many decks. Each card has an integer from 0-12 associated it which is called its "converted mana cost" or CMC. That's all you need to know. Don't bother learning to play Magic on my account.

我想要的内容可能类似于：

And what I want might look something like:

 deck | cmc | perc
------+-------+-------
    1 | 1   |  11
    1 | 2   |  11
    1 | 3   |  11
    1 | 4   |  11
    1 | 5   |  11

第一行的 perc表示卡片组中11％的卡片具有id 1具有cmc 1。

Where "perc" in the first row says that 11 percent of the cards in the deck with with the id 1 have cmc 1.

推荐答案

解决了！

SELECT
    d.id,
    c.cmc,
    (CAST(SUM(m.count) AS FLOAT) / 
        (SELECT
            CAST(SUM(m1.count) AS FLOAT)
        FROM deckmembers AS m1
        JOIN cards AS c1 ON c1.id=m1.card
        WHERE NOT m1.sideboard
        AND c1.fulltype NOT LIKE '%Land%'
        AND m1.deck=d.id)
    ) * 100 AS perc
FROM deckmembers AS m
JOIN decks AS d ON d.id=m.deck
JOIN cards AS c ON c.id=m.card
WHERE NOT m.sideboard
AND c.fulltype NOT LIKE '%Land%'
GROUP BY d.id, c.cmc;

我还发布了该问题的简单版本的解决方案在每个邻域中找到区域的比例。

I also posted a solution to the simpler version of the problem here.

这篇关于在PostgreSQL中找到每个由Y组成的X的比例？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！