python - 来自 Pandas 的 NTILE for Sqlite 给出操作错误

Question

我正在尝试使用 NTILE 函数从 pandas 查询 SQLite 数据库，但我没有成功，尽管我已经多次重新检查了语法。

下面的独立示例。设置:

import pandas as pd
from sqlalchemy import create_engine
disk_engine = create_engine('sqlite:///test.db')

marks = pd.DataFrame({'StudentID': ['S1', 'S2', 'S3', 'S4', 'S5'],
                      'Marks': [75, 83, 91, 83, 93]})
marks.to_sql('marks_sql', disk_engine, if_exists='replace')

现在尝试使用 NTILE:

q = """select StudentID, Marks, NTILE(2) OVER (ORDER BY Marks DESC)
        AS groupexample FROM marks_sql"""
pd.read_sql_query(q, disk_engine)

回溯很长，但主要部分是:

OperationalError: near "(": syntax error
OperationalError: (sqlite3.OperationalError) near "(": syntax error [SQL: 'select StudentID, Marks, NTILE(2) OVER (ORDER BY Marks DESC)\n        AS groupexample FROM marks_sql']

谢谢!

Answer 1

SQLITE 中没有NTILE () OVER 功能

给我同样的错误，需要使用更复杂的查询或函数来创建它

Here is a list of unsupported analytical functions在 SQLITE 中不可用

NTILE 就是其中之一

优化器首先进入查询以找到 OVER，它认为这是一个列名并且不期望 ( 跟随一个列名，所以给你这个错误。

要复制 NTILE，试试这个:

select * ,
case 
  when 
    (select count(*)+0.0 from marks_sql b where table.Marks >= b.Marks)
    /(select count(*) from marks_sql ) >0.5 
  then 1 
  else 2 end
from marks_sql;

为了以这样的方式做到这一点，即表格可以变大并且这种技术仍然适用，我们做了一些事情:

所以首先我们按 Marks 对表格进行排序(实质上是创建排名)。这将计算具有更高或等于 Marks 的行:

select count(*)+0.0 from marks_sql b where table.Marks >= b.Marks  --rank of Mark

我们添加 0.0 使这个数字成为 float ，以便我们的分数在下一步中起作用。

然后我们将排名除以总行数

select count(*) from marks_sql -- row count

这为我们提供了分数范围内的分布，即每个学生的百分位数。但是我们不关心每个精确的百分位数，我们关心的是 NTILE(2) 或者它们是否在上半部分。

这就是 CASE 语句发挥作用的地方。如果学生的百分位数超过 50%，则他们属于第一组，即前 50 个百分位数。其他人都属于 #2 组。