在SQLite中创建数据透视表的最佳方法

本文关键字：最佳 方法 透视 数据 SQLite 创建 | 更新日期: 2023-09-27 18:04:32

我正在使用c#和SQLite来切片大量数据，并且我经常需要以数据透视表的形式显示数据。通过使用c#从另一个查询创建SQL命令，我可以轻松地使我的枢轴动态，但我仍然无法决定以哪种方式进行枢轴本身，所以我想听听比我更有经验的程序员对这件事的一些意见。

我心中有三个方法。假设我们有一个名为tData的简单表，它有三列:"row"表示该数据的行号，"col"表示列号，"val"表示值。

传统方法是使用CASE表达式:

SELECT
      row,
      sum(CASE col WHEN 1 THEN val END) AS col1,
      sum(CASE col WHEN 2 THEN val END) AS col2,
      sum(CASE col WHEN 3 THEN val END) AS col3
FROM tData
GROUP BY row

然而，我在想，如果我放弃CASE语句并直接对值使用逻辑表达式，利用true==1和false==0的事实，可能会更快:

SELECT
      row,
      sum((col=1)*val) AS col1,
      sum((col=2)*val) AS col2,
      sum((col=3)*val) AS col3
FROM tData
GROUP BY row

我怀疑这个方法应该更快，因为CASE表达式应该有一些开销，但我不是很确定。

第三种方法稍微复杂一些:它使用join来进行透视:

SELECT
      rows.row,
      col1.valSum AS col1,
      col2.valSum AS col2,
      col3.valSum AS col3
FROM
    (SELECT row FROM tData GROUP BY row) AS rows
LEFT JOIN
    (SELECT row,sum(val) AS valSum FROM tData WHERE col=1 GROUP BY row) AS col1
    ON rows.row=col1.row
LEFT JOIN
    (SELECT row,sum(val) AS valSum FROM tData WHERE col=2 GROUP BY row) AS col2
    ON rows.row=col2.row
LEFT JOIN
    (SELECT row,sum(val) AS valSum FROM tData WHERE col=3 GROUP BY row) AS col3
    ON rows.row=col3.row

没错，这些join有很大的开销，但是从我有限的经验来看，在处理大型表时，SQL实现可以执行简单的筛选-分组和求和操作，比自定义数据-每行操作快得多，而且这远远弥补了开销。问题是，这类SQL语句生成起来更复杂，因为每个列在语句中的两个位置出现——一次在fields子句中，一次在FROM子句中，而不是像前两个方法那样只在fields子句中。另外，我需要小心所有这些临时表的名称。

有什么意见吗?

在SQLite中创建数据透视表的最佳方法

我希望case语句方法的执行速度比对表执行尽可能多的分组连接(groupby -and-join)更快，因为所讨论的列中有不同的值。前者是CPU密集型的，后者是磁盘密集型的。例如，如果要成为列头的列值包含一周中的某一天，那么将有七个主透视列和七个选择分组。这可能会很昂贵;这取决于表的大小

看起来您正在使用EAV设计，这使得有必要将行透视到列中。在适当的关系数据库设计中，不应该使用EAV。列就是列，你不需要透视。

也就是说，我理解EAV有时是最小的缺点，当需要在数据库中存储一组"可扩展"属性时，它是一种流行的设计。

获取数据最有效的方法是忘记在SQL中执行pivot。只需获取您的属性作为row的每个给定值的多行:

SELECT row, col, val FROM tData WHERE row = ...

然后在c#应用程序中编写代码来遍历产生的多行结果集。为每个不同的row创建一个新对象。设置对象的"col"字段为"val"。然后继续获取查询结果的下一行。

这样做有以下优点:

  • 查询编写简单。在选择列表中只需要命名三个列，不需要列别名。
  • RDBMS执行该查询的成本不高。无GROUP BY，无自连接等
  • 仍然支持EAV设计的可扩展优势。实际上，它更容易扩展，因为当您向数据添加新的逻辑列时，您不必重写SQL查询。
相关文章：