sql - 来自规范化 SQL 数据库的数据的表格 View

Question

我已尝试创建规范化数据库，但无法以表格格式显示数据。

在我下面的示例中，数据库用于按日期跟踪数字(在各种任意命名的类别中)。例如，用户可以跟踪每天有多少水果和蔬菜被送到他的杂货店。用户定义水果和蔬菜类别的名称，以及存在多少类别。以下是与此示例对应的表格:

一个跟踪器表:

 id |      name       
----+---------------------
  1 | Grocery deliveries 
  2 | Sports cars

一个条目表:

 id |      datetime       | tracker_id 
----+---------------------+------------
  1 | 2013-10-01 00:00:00 |          1
  2 | 2013-10-02 00:00:00 |          1
  3 | 2013-10-03 00:00:00 |          1

一个值表:

 id | number | entry_id | category_id 
----+--------+----------+-------------
  1 |   10.0 |        1 |          1
  3 |   20.0 |        1 |          2
  5 |   21.0 |        1 |          3
  7 |   18.0 |        2 |          2
  8 |    4.0 |        3 |          1
  9 |    9.0 |        3 |          2

还有一个类别表:

 id |     name        | tracker_id 
----+-----------------+------------
  1 | Tomatoes        |          1
  2 | Carrots         |          1
  3 | Brussel sprouts |          1
  4 | Ferraris        |          2

我想为跟踪器 1 打印一个表格，每行对应一个日期(无重复日期)。这些列将是:日期、类别 1(西红柿)、类别 2(胡萝卜)、类别 3(抱子甘蓝)。如果给定日期的给定类别没有值，它将为空或显示为 null。所以，理想情况下，它看起来像这样:

 datetime            | Tomatoes | Carrots | Brussel sprouts
---------------------+----------+---------+-----------------
 2013-10-01 00:00:00 | 10.0     | 20.0    | 21.0
 2013-10-02 00:00:00 | Null     | 18.0    | Null
 2013-10-03 00:00:00 | 4.0      | Null    | 9.0

我不确定该怎么做，或者是否有更好的方法来存储这些数据。有什么建议吗？

当 entries 和 values 由单个表表示时(条目是行，值是列)，显示数据很容易。但在那种情况下，类别的最大数量受表中列数的限制。我更喜欢规范化方法如何允许每个“跟踪器”代表任意数量的类别。

Answer 1

按照@PM77-1 的建议，我想出了一个使用 PostgreSQL 的 crosstab 函数的替代方法。

具体来说，我使用的是 crosstab(text source_sql, text category_sql)函数的形式，如下:

SELECT * FROM 
  crosstab('SELECT e.datetime, v.category_id, v.number 
            FROM entries e, values v 
            WHERE v.entry_id = e.id AND e.tracker_id = 1 ORDER BY 1, 2', 
           'SELECT id FROM categories WHERE tracker_id = 1 ORDER BY 1')
  AS (row_name timestamp without time zone,
      tomatoes numeric,
      carrots numeric,
      brussel_sprouts numeric);

使用这种方法，AS (...) 项对于每个跟踪器必须是唯一的，因为每个跟踪器的类别数量及其名称可能不同。在我的例子中，我使用 Python 和 psycopg2 模块执行查询，因此动态生成查询很简单。例如，

# Retrieve the category names for the current tracker
cur.execute("SELECT name FROM categories WHERE tracker_id = " + 
            str(tracker_id) + ";")
categories = cur.fetchall()
category_count = len(categories)

# Generate category string
cat_str = '';
for n in range(category_count):
    cat_str = cat_str + ", cat_" + str(n) + " numeric"

cur.execute("SELECT * FROM crosstab("
            "'SELECT e.datetime, v.category_id, v.number FROM entries e, values v"
            " WHERE v.entry_id = e.id"
            " AND e.tracker_id = " + str(tracker_id) +
            " ORDER BY 1, 2;',"
            " 'SELECT id FROM categories WHERE tracker_id =" + 
            str(tracker_id) + "')"
            " AS (row_name timestamp without time zone" + cat_str + ");")

results = cur.fetchall()

结果具有通用列名 cat_0、cat_1 等，而不是 tomatoes、carrots 等。但是，我将 categories 和 results 都传递给 HTML 模板以呈现具有正确标题的表格。