python - SQLite:转置 GROUP BY 的结果并用名称填充 ID

Question

我的问题比较具体，如果您有更好的标题，请推荐一个。另外，格式很糟糕 - 不知道如何组合列表和代码块。

我有一个 SQLite3 数据库，具有以下(相关部分).schema:

CREATE TABLE users (id INTEGER PRIMARY KEY NOT NULL, user TEXT UNIQUE);
CREATE TABLE locations (id INTEGER PRIMARY KEY NOT NULL, name TEXT UNIQUE);
CREATE TABLE purchases (location_id INTEGER, user_id INTEGER);
CREATE TABLE sales (location_id integer, user_id INTEGER);

购买 约有 450 万条条目，用户 约 30 万，销售 约 10 万，地点 约 250 个- 只是为了衡量数据量。

我想要的用途是生成一个 JSON 对象，并将其传递给另一个应用程序，通过执行以下操作，体积会非常压缩:

-按 location_id、user_id 将购买和销售分组到一个公用表中 - IOW，获取每个位置每个用户的“操作”数量。我能做到，结果是这样的

loc  | usid  | loccount
-----------------------
1    | 1246  | 123
1    | 2345  | 1
13   | 1246  | 46
13   | 8732  | 4
27   | 2345  | 41

(至少它看起来不错，但这样的体积总是很难判断；查询:

select location_id,user_id,count(location_id) from
  (select location_id,user_id from purchases
     union all
   select location_id,user_id from sales)
group by location_id,user_id order by user_id`

)

-然后，调换那个巨大的 table ，这样我就可以得到:

usid | loc1 | loc13 | loc27
---------------------------
1246 | 123  | 46    | 0
2345 | 1    | 0     | 41
8732 | 0    | 4     | 0

我做不到，这是我对这个问题的绝对关键点。我尝试了一些在网上找到的东西，尤其是在这里，但我刚刚开始使用 SQLite 不久，并且不理解很多查询。

-最后，将表格转换为纯文本，以便将其写入 JSON:

user | AAAA | BBBBB | CCCCC
---------------------------
zeta | 123  | 46    | 0
beta | 1    | 0     | 41
iota | 0    | 4     | 0

我可能可以通过大量的实验和内部连接来完成，尽管我总是非常不确定处理此类数据量的最佳方法是什么，因此我不介意一个指针。

如果重要的话，整个事情都是用 Python 的 sqlite3 接口(interface)编写的。最后，我希望能够为每个用户执行一个“for”循环来生成 JSON，这当然会非常简单。如果查询需要很长时间(<10 分钟就可以了)也没关系，它每天只运行两次作为一种备份。我只有一个小型 VPS，但由于仅限于单个核心，因此性能与我相当强大的台式机一样好。 (运行 Debian 的 i5-3570k。)

表头只是示例，因为我不太确定是否可以为它们使用整数(如果可以，则没有发现语法)，只要我能够以某种方式查找表中的数字部分即可位置表我很好。将用户 ID 转换为名称也是如此。列数是事先已知的 - 它们毕竟只是整数主键，并且我有一个来自其他操作的 list() 。如果需要的话，可以相当快地确定行数，约 3 秒。

Answer 1

考虑使用子查询来实现所需的转置输出:

SELECT DISTINCT m.usid,

   IFNULL((SELECT t1.loccount FROM tablename t1 
         WHERE t1.usid = m.usid AND t1.loc=1),0) AS Loc1,
   IFNULL((SELECT t2.loccount FROM tablename t2 
         WHERE t2.usid = m.usid AND t2.loc=13),0) AS Loc13,
   IFNULL((SELECT t3.loccount FROM tablename t3 
         WHERE t3.usid = m.usid AND t3.loc=27),0) AS Loc27

FROM tablename As m

或者，您可以使用嵌套的 IF 语句(或者在 SQLite 中使用 CASE/WHEN 的情况下)作为派生表:

SELECT temp.usid, Max(temp.loc1) As Loc1, 
       Max(temp.loc13) As Loc13, Max(temp.loc27) As Loc27
FROM    
   (SELECT tablename.usid, 
      CASE WHEN loc=1 THEN loccount ELSE 0 As Loc1 END,  
      CASE WHEN loc=13 THEN loccount ELSE 0 As Loc13 END, 
      CASE WHEN loc=27 THEN loccount ELSE 0 As Loc27 END
    FROM tablename) AS temp    
GROUP BY temp.usid