我在jupyter笔记本中通过Pandas在SQLite中进行了一个查询。
以下查询按预期运行,只是我希望dayofweek_count计算每个工作日的发生次数。每天可以有多个条目(即,20年2月17日星期一可以有4个条目,但我只想20年2日星期一计数一次(
df_total_weekday=pd.read_sql_query("SELECT CASE cast(strftime('%w', event_date) as integer)"
"when 0 then 'Sunday'"
"when 1 then 'Monday'"
"when 2 then 'Tuesday'"
"when 3 then 'Wednesday'"
"when 4 then 'Thursday'"
"when 5 then 'Friday'"
"else 'Saturday' end as dayofweek"
",COUNT(strftime('%w', event_date)) as dayofweek_count"
",SUM(quantity) as total_quantity FROM subscription_data GROUP BY 1 ORDER BY strftime('%w', event_date)",conn,parse_dates=['event_date'])
但是这个查询在DISTINCT附近抛出了一个语法错误,但我似乎不明白为什么。我已经尝试了DISTINCT(event_date(和DISTINCT event_date。
df_total_weekday=pd.read_sql_query("SELECT CASE cast(strftime('%w', event_date) as integer)"
"when 0 then 'Sunday'"
"when 1 then 'Monday'"
"when 2 then 'Tuesday'"
"when 3 then 'Wednesday'"
"when 4 then 'Thursday'"
"when 5 then 'Friday'"
"else 'Saturday' end as dayofweek"
",COUNT(strftime('%w',DISTINCT event_date)) as dayofweek_count"
",SUM(quantity) as total_quantity FROM subscription_data GROUP BY 1 ORDER BY strftime('%w', event_date)",conn,parse_dates=['event_date'])
我错过了什么?
COUNT(strftime('%w',DISTINCT event_date))
这是一个语法错误,因为strftime()
不理解DISTINCT
;它不是一个聚合函数(对于聚合函数,它只适用于具有单个参数的函数(。除非您的某些event_date
值不被接受的日期/时间字符串(这将使strftime()
返回NULL
(,否则COUNT(strftime(...))
无论如何都没有多大意义。