时间戳

首页 > 解决方案 > sql如何选择行=值的多行计数

python - sql如何选择行=值的多行计数

问题描述

嗨，我正在为 Domain == 'whatever' 的多个行值的计数进行 sql 选择。

目前，我进行了 5 个单独的选择，这些选择需要很长时间才能加载并且效率低下，我问我是否以及如何在一个查询中选择多行的计数，或者至少比我现在做的更有效。

我正在尝试选择行数：路由、浏览器、设备、位置和引荐来源网址，其中域等于“随便”

我希望输出包含上面列出的所有行，格式如下：rowname：{count：1}

这是表格：

CREATE TABLE IF NOT EXISTS hits (
    ID INTEGER PRIMARY KEY AUTO_INCREMENT,
    Domain TEXT NOT NULL,
    Route TEXT NOT NULL,
    Timestamp INTEGER NOT NULL,
    Browser TEXT,
    Location TEXT,
    Device TEXT,
    Referrer TEXT
)

这些是我目前的选择：

    routes = db.run('SELECT Route, COUNT(Route) AS count FROM hits WHERE Domain = %s AND Timestamp BETWEEN %s AND %s GROUP BY Route', (domain, d1, d2))

    browsers = db.run('SELECT Browser, COUNT(Browser) AS count FROM hits WHERE Domain = %s AND Timestamp BETWEEN %s AND %s GROUP BY Browser', (domain, d1, d2))

    locations = db.run('SELECT Location, COUNT(Location) AS count FROM hits WHERE Domain = %s AND Timestamp BETWEEN %s AND %s GROUP BY Location', (domain, d1, d2))


    screens = db.run('SELECT Device, COUNT(Device) AS count FROM hits WHERE Domain = %s AND Timestamp BETWEEN %s AND %s GROUP BY Device', (domain, d1, d2))


    referrals = db.run('SELECT Referrer, COUNT(Referrer) AS count FROM hits WHERE Domain = %s AND Timestamp BETWEEN %s AND %s GROUP BY Referrer', (domain, d1, d2))```

标签： pythonmysqlsql

解决方案

以下适用于 SQL 小提琴。该问题使用 mysql 和 python 标记，因此如果您需要进一步的帮助，请进一步指定您使用的引擎：

SELECT
  COUNT(Route) AS route_count, 
  COUNT(Browser) AS browser_count,
  COUNT(Location) AS location_count,
  COUNT(Device) AS device_count,
  COUNT(Referrer) AS referrer_count

FROM 
  hits 
WHERE 
  Domain = 'whatever'
  AND Timestamp BETWEEN '00:00' AND '12:00'

推荐阅读