我試圖在 select 陳述句中按用戶 ID 和小時來平均一些指標。
希望在不使用 python 腳本中的引數的情況下了解執行此操作的最佳方法：P
這是當前的查詢，
SELECT
user_id,
AVG(sentiment) as sentiment,
AVG(magnitude) as magnitude,
SUM(sentiment) as total_sentiment,
SUM(magnitude) as total_magnitude,
MAX(sentiment) as max_sentiment,
MIN(sentiment) as min_sentiment,
COUNT(user_id) as count
FROM
sentiments
WHERE
created
BETWEEN %s and %s
GROUP BY
user_id;
順便說一句，Postgres，并通過 python 發送帶有 pandas 的查詢進行測驗:)
更新嘗試答案，
sql = """
SELECT
user_id,
AVG(sentiment) as sentiment,
AVG(magnitude) as magnitude,
SUM(sentiment) as total_sentiment,
SUM(magnitude) as total_magnitude,
MAX(sentiment) as max_sentiment,
MIN(sentiment) as min_sentiment,
COUNT(user_id) as count
FROM
sentiments
GROUP BY
user_id,
date_part('hour', created);
"""
conn = db_conn.main()
cur = conn.cursor()
df = pd.read_sql(sql, con=conn)
cur.close()
df
這會回傳一個錯誤
': function date_part(unknown, integer) does not exist
LINE 15: date_part('hour', created);
^
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
提前致謝，
uj5u.com熱心網友回復：
截斷時間戳的最簡單方法是DATE_TRUNC函式。
SELECT DATE_TRUNC('hour', created), user_id,
...
GROUP BY DATE_TRUNC('hour', created), user_id;
但是如果created包含一個unix時間戳的數字，那么您首先需要將其轉換為時間戳。
SELECT DATE_TRUNC('hour', TO_TIMESTAMP(created)), user_id,
...
GROUP BY DATE_TRUNC('hour', TO_TIMESTAMP(created)), user_id;
uj5u.com熱心網友回復：
在 posgresql 中，您可以使用以下方法按年、月、日和小時分組，
group by
date_part('year', created),
date_part('month', created),
date_part('day', created),
date_part('hour', created)
或者用 epoch 做一些數學運算
group by floor(date_part('epoch', created_at)/3600)
或者，您可以使用提取物，例如 extract(epoch from created)
uj5u.com熱心網友回復：
有幾種方法可以解釋按小時分組可能有用。下面是用于將時間舍入或截斷為一小時的 SQL。這將為您提供每小時有資料的時間戳：
SELECT
user_id,
AVG(sentiment) as sentiment,
AVG(magnitude) as magnitude,
SUM(sentiment) as total_sentiment,
SUM(magnitude) as total_magnitude,
MAX(sentiment) as max_sentiment,
MIN(sentiment) as min_sentiment,
COUNT(user_id) as count,
date_trunc('hour',created) hourcreated
FROM
sentiments
WHERE
created BETWEEN %s and %s
GROUP BY date_trunc('hour',created), user_id
ORDER BY date_trunc('hour',created), user_id;
您可能還想查看您的資料在平均一天的情況，例如，您可能想尋找下午的峰值。
在這種情況下，您將通過提取小時獲得最多 24 個結果。將date_trunc('hour',created)上面替換為下面
extract(hour from created)
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/408978.html
標籤：