SQL:WHERE 语句能否过滤掉 GROUP BY 语句的特定组 [英] SQL: Can WHERE Statement filter out specific groups for GROUP BY Statement

问题描述

我对 SQL 还是个新手，并在 SQLite3 测试数据库中玩.我尝试在网上挖掘这个答案，但找不到答案.我无法让我的测试查询在 GROUP BY 语句之前使用 WHERE 排除某些组.

I'm still new to SQL and playing inside a SQLite3 test database. I tried digging online for this answer but could not find an answer. I cannot get my test query to exclude certain groups using WHERE before a GROUP BY Statement.

这是我的查询:

SELECT USER_ID, SESSION_ID, MAX(SESSION_DURATION), MAX(TIME_STAMP)
FROM table
WHERE SESSION_ID <> 0
GROUP BY USER_ID, SESSION_ID

基本上在我的数据库中，有会话 ID 为 0 的行.我想在尝试将它们分组之前排除这些行.但是，结果返回会话 id = 0 的组

Basically in my database, there are rows where session ID is 0. I would like to exclude those rows before applying trying to group them up. However, the result returns groups with session id = 0

感谢您的帮助！

更新 1

类似于 sqlfiddle(下)，在创建一个简单的测试数据库时，查询实际上是有效的.

Similar to sqlfiddle (Below), while creating a simple test database the query actually works.

我实际上是在使用 Python 和 SQLite3 库将一个 15,000 行的 CSV 文件导入数据库并运行查询.显然，自从我的测试表正常工作以来，我的导入代码是有问题的.我会让你们所有人都知道我的发现.谢谢大家的帮助.

I am actually using Python and the SQLite3 library to import a 15 K row CSV file into the database and running the query. Obviously something is breaking cause of my import code since my Test table that is working. I will keep you all posted on what I discover. Thank you everyone for your help.

更新 2

我现在已经使用相同的测试表测试了我的导入代码，并且查询使用这个非常简单的 CSV 文件过滤掉组 0.不幸的是，我不能发布多个链接，否则我将发布我的简单测试 CSV

I have now tested my import code with the same test table and the query works filtering out group 0 using this very simple CSV file. Unfortunately, I cannot post multiple links else I will post my simple test CSV

但是，对于我的 15 K CSV 文件，它不起作用.这是 15 K 文件的链接https://dl.dropboxusercontent.com/u/69835430/sql_session_data.csv

However, with my 15 K CSV file, it does not work. Here is a link to the 15 K file https://dl.dropboxusercontent.com/u/69835430/sql_session_data.csv

有没有我遗漏的特定边缘情况?

Is there any specific edge case I missed?

更新 3 - 最终答案

感谢 CL 在下面关于我的 INTEGER 列中的字符串值的回答.我仔细研究了我正在使用的 SQLite3 模块.

Thanks to CL's answer below about a string value in my INTEGER column. I dug around about the SQLite3 module I was using.

我使用 sqlite3.dictreader 并传入默认读取值.我通过在将数据放入数据库之前解析我的 CSV 文件时添加一个 == '' 检查来解决这个问题.

I was using the sqlite3.dictreader and passing in the default read value. I fixed this by adding a == '' check when parsing my CSV file before putting the data into the DB.

感谢大家的帮助！

推荐答案

> SELECT 0 <> 0;
0
> SELECT '0' <> 0;
1

您的数据库中有字符串值.修复它们:

You have string values in your database. Fix them:

UPDATE MyTable
SET Session_ID = CAST(Session_ID AS INTEGER);

这篇关于SQL:WHERE 语句能否过滤掉 GROUP BY 语句的特定组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！