如何计算两个表的日期的最小值？

莫妮卡的革命

我有一个 MySQL 5.7 查询，它告诉我至少一年前有多少人连接自己或阅读电子邮件。

select lang, count(distinct user_id)
from ((select user_id, lang
       from sessions
       where session_time > now() - interval 1 year
      ) union all
      (select user_id, lang
       from unique_open_emails
       where date > now() - interval 1 year
      )
     ) u
group by lang;

我想用 Python 更新它，以便每天进行此计算，直到一个或另一个表中的最后一个日期。所以我做了：

with db as c:
    info("Creating table and populating `alive_subscribers` ...")
    c.execute("""CREATE TABLE IF NOT EXISTS alive_subscribers Date AS DATE,
                                                              Lang AS CHAR(2),
                                                              Count AS INTEGER;""")

    sdate = c.execute(
        """
        SELECT MIN(date_email, date_activity) FROM 
            (
                SELECT date FROM unique_open_emails
                ORDER BY date ASC LIMIT 1;
            ) AS date_email,
            (
                SELECT session_time FROM sessions
                ORDER BY session_time ASC LIMIT 1;
            ) AS date_activity
        )"""
    )  # start date
    edate = date.now()
    date_list = pd.date_range(sdate,edate-timedelta(days=1),freq='d')
    for date_range in date_list:
        c.execute(
            f"""
            insert into ...
            select lang, count(distinct user_id)
            from ((select user_id, lang
                   from sessions
                   where session_time > now() - interval 1 year
                  ) union all
                  (select user_id, lang
                   from unique_open_emails
                   where date > {date_range} - interval 1 year
                  )
                 ) u
            group by lang;""")

但是当我尝试获取两个数据库日期的最小值时它返回一个错误：

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' date_activity) FROM 
    (
        SELECT date FROM unique_open_emails
        ' at line 1

这是MySQL Fiddle和数据库架构：

CREATE TABLE sessions
    (`App Type` varchar(11), `Lang` varchar(2), `Session ID` int, `session_time` datetime, `user_id` int)
;
    
INSERT INTO sessions
    (`App Type`, `Lang`, `Session ID`, `session_time`, `user_id`)
VALUES
    #july 2021
    ('browser', 'fr', 46960339, '2021-07-29 00:00:00', 1292997),
    ('browser', 'es', 46960407, '2021-07-29 00:00:00', 1055040),
    ('pwa_android', 'fr', 46960412, '2021-07-29 00:00:00', 1120109),
    ('browser', 'fr', 46960391, '2021-07-29 00:00:00', 955480),
    ('pwa_android', 'en', 46960389, '2021-07-29 00:00:00', 800169),
    ('browser', 'es', 46960353, '2021-07-29 00:00:00', 1193745),
    # june 2021
    ('browser', 'fr', 46960339, '2021-06-29 00:00:00', 1292997),
    ('browser', 'es', 46960407, '2021-06-29 00:00:00', 1055040),
    ('pwa_android', 'fr', 46960412, '2021-06-29 00:00:00', 1120109),
    ('browser', 'fr', 46960391, '2021-06-29 00:00:00', 955480),
    ('pwa_android', 'en', 46960389, '2021-06-29 00:00:00', 800169),
    ('browser', 'es', 46960353, '2021-06-29 00:00:00', 12345),
    # june 2020
    ('browser', 'fr', 46960339, '2020-06-29 00:00:00', 1292997),
    ('browser', 'es', 46960407, '2020-06-29 00:00:00', 1055040),
    ('pwa_android', 'fr', 46960412, '2020-06-29 00:00:00', 1120109),
    ('browser', 'fr', 46960391, '2020-06-29 00:00:00', 955480),
    ('pwa_android', 'en', 46960389, '2020-06-29 00:00:00', 800169),
    ('browser', 'es', 46960353, '2020-06-29 00:00:00', 54321)
;

SELECT * FROM sessions;

CREATE TABLE unique_open_emails
    (`date` datetime, `lang` varchar(2), `user_id` int)
;
    
INSERT INTO unique_open_emails
    (`date`, `lang`, `user_id`)
VALUES
    ('2016-04-12 00:00:00', 'fr', 115434),
    ('2016-04-13 00:00:00', 'fr', 11357),
    ('2016-04-13 00:00:00', 'fr', 137481),
    ('2016-04-13 00:00:00', 'fr', 10296),
    ('2016-04-13 00:00:00', 'fr', 125772),
    ('2016-04-13 00:00:00', 'fr', 955480),
    ('2016-04-13 00:00:00', 'fr', 9269),
    ('2016-04-13 00:00:00', 'fr', 90716),
    ('2016-04-13 00:00:00', 'fr', 26330),
    ('2016-04-13 00:00:00', 'fr', 89072),
    ('2016-04-13 00:00:00', 'fr', 87416),
    ('2016-04-13 00:00:00', 'fr', 88358),
    ('2016-04-13 00:00:00', 'fr', 102515),
    ('2016-04-13 00:00:00', 'fr', 89867),
    ('2016-04-13 00:00:00', 'fr', 119146),
    ('2016-04-13 00:00:00', 'fr', 133316),
    ('2016-04-13 00:00:00', 'fr', 90095),
    ('2016-04-13 00:00:00', 'fr', 16510),
    ('2016-04-13 00:00:00', 'fr', 21530),
    ('2016-04-13 00:00:00', 'fr', 81581),
    ('2016-04-13 00:00:00', 'es', 54321),
    ('2016-04-13 00:00:00', 'fr', 29363),
    ('2016-04-13 00:00:00', 'fr', 90326),
    ('2016-04-13 00:00:00', 'fr', 23961),
    ('2016-04-13 00:00:00', 'fr', 89000),
    ('2016-04-13 00:00:00', 'fr', 9484),
    ('2016-04-13 00:00:00', 'fr', 11845),
    ('2016-04-13 00:00:00', 'fr', 41231),
    ('2016-04-13 00:00:00', 'fr', 588),
    ('2016-04-13 00:00:00', 'fr', 16678),
    ('2016-04-13 00:00:00', 'fr', 19674),
    ('2016-04-13 00:00:00', 'fr', 130113),
    ('2016-04-13 00:00:00', 'fr', 84719),
    ('2016-04-13 00:00:00', 'fr', 123252),
    ('2016-04-13 00:00:00', 'fr', 4676),
    ('2016-04-13 00:00:00', 'fr', 113636),
    ('2016-04-13 00:00:00', 'fr', 17452),
    ('2016-04-13 00:00:00', 'fr', 136544),
    ('2016-04-13 00:00:00', 'fr', 15917),
    ('2016-04-13 00:00:00', 'fr', 82787),
    ('2016-04-13 00:00:00', 'fr', 81620),
    ('2016-04-13 00:00:00', 'fr', 135298),
    ('2016-04-13 00:00:00', 'fr', 15643),
    ('2016-04-13 00:00:00', 'fr', 80981),
    ('2016-04-13 00:00:00', 'fr', 51827),
    ('2016-04-13 00:00:00', 'fr', 90554),
    ('2016-04-13 00:00:00', 'fr', 10277),
    ('2016-04-13 00:00:00', 'fr', 24432),
    ('2016-04-13 00:00:00', 'fr', 6651),
    ('2016-04-13 00:00:00', 'fr', 64106),
    ('2016-04-13 00:00:00', 'fr', 119080),
    ('2016-04-13 00:00:00', 'fr', 72659),
    ('2016-04-13 00:00:00', 'fr', 130004),
    ('2016-04-13 00:00:00', 'fr', 22320),
    ('2016-04-13 00:00:00', 'fr', 136966),
    ('2016-04-13 00:00:00', 'fr', 11317),
    ('2016-04-13 00:00:00', 'fr', 79031),
    ('2016-04-13 00:00:00', 'fr', 90800),
    ('2016-04-13 00:00:00', 'fr', 16149),
    ('2016-04-13 00:00:00', 'fr', 61463),
    ('2016-04-13 00:00:00', 'fr', 5383),
    ('2016-04-13 00:00:00', 'fr', 87674),
    ('2016-04-13 00:00:00', 'fr', 10223),
    ('2016-04-13 00:00:00', 'fr', 88100),
    ('2016-04-13 00:00:00', 'fr', 91691),
    ('2016-04-13 00:00:00', 'fr', 89423),
    ('2016-04-13 00:00:00', 'fr', 126),
    ('2016-04-13 00:00:00', 'fr', 86858),
    ('2016-04-13 00:00:00', 'fr', 1419),
    ('2016-04-13 00:00:00', 'fr', 89849),
    ('2016-04-13 00:00:00', 'fr', 15721),
    ('2016-04-13 00:00:00', 'fr', 86444),
    ('2016-04-13 00:00:00', 'fr', 130822),
    ('2016-04-13 00:00:00', 'fr', 73991),
    ('2016-04-13 00:00:00', 'fr', 113969),
    ('2016-04-13 00:00:00', 'fr', 16779),
    ('2016-04-13 00:00:00', 'fr', 71267),
    ('2016-04-13 00:00:00', 'fr', 61067),
    ('2016-04-13 00:00:00', 'fr', 89081),
    ('2016-04-13 00:00:00', 'fr', 24815),
    ('2016-04-13 00:00:00', 'fr', 91928),
    ('2016-04-13 00:00:00', 'fr', 13071),
    ('2016-04-13 00:00:00', 'fr', 1942),
    ('2016-04-13 00:00:00', 'fr', 44012),
    ('2016-04-13 00:00:00', 'fr', 52049),
    ('2016-04-13 00:00:00', 'fr', 6626),
    ('2016-04-13 00:00:00', 'fr', 7034),
    ('2016-04-13 00:00:00', 'fr', 20442),
    ('2016-04-13 00:00:00', 'fr', 75422),
    ('2016-04-13 00:00:00', 'fr', 16673),
    ('2016-04-13 00:00:00', 'fr', 17325),
    ('2016-04-13 00:00:00', 'fr', 7898),
    ('2016-04-13 00:00:00', 'fr', 85226),
    ('2016-04-13 00:00:00', 'fr', 136557),
    ('2016-04-13 00:00:00', 'fr', 134423),
    ('2016-04-13 00:00:00', 'fr', 68723),
    ('2016-04-13 00:00:00', 'en', 118331),
    ('2016-04-13 00:00:00', 'fr', 91298),
    ('2016-04-13 00:00:00', 'fr', 136046),
    ('2016-04-13 00:00:00', 'fr', 136891),
    ('2016-04-13 00:00:00', 'fr', 9169),
    ('2016-04-13 00:00:00', 'fr', 88946),
    ('2016-04-13 00:00:00', 'fr', 115919),
    ('2016-04-13 00:00:00', 'fr', 44492),
    ('2016-04-13 00:00:00', 'fr', 89783),
    ('2016-04-13 00:00:00', 'fr', 137482),
    ('2016-04-13 00:00:00', 'fr', 10072),
    ('2016-04-13 00:00:00', 'fr', 38636),
    ('2016-04-13 00:00:00', 'fr', 11227),
    ('2016-04-13 00:00:00', 'fr', 108310),
    ('2016-04-13 00:00:00', 'fr', 4700),
    ('2016-04-13 00:00:00', 'fr', 17976),
    ('2016-04-13 00:00:00', 'fr', 8580),
    ('2016-04-13 00:00:00', 'fr', 91316)
;

nbk

我不确定你的期望，但你有很多错误

Min 的值不超过一个，分号只属于查询的末尾

       SELECT IF(date < session_time,date, session_time) as date FROM 
            (
                SELECT date FROM unique_open_emails
                ORDER BY date ASC LIMIT 1
            ) AS date_email,
            (
                SELECT session_time FROM sessions
                ORDER BY session_time ASC LIMIT 1
            ) AS date_activity

| 日期 | 
| :------------------ | 
| 2016-04-12 00:00:00 |

db<>在这里小提琴

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。

编辑于 2021-09-9

我来说两句

0 条评论

登录后参与评论

上一篇：单个 Prisma 查询中的左连接和聚合

如何用标准公式计算两个范围的成对最小值的总和？

如何计算两个表的日期的最小值？

如何计算两个表的日期的最小值？

蓝屏死机没有修复解决方案

计算数据帧中每行的NA

UITableView的项目向下滚动后更改颜色，然后快速备份

Node.js中未捕获的异常错误，发生调用

在 Python 2.7 中。如何从文件中读取特定文本并分配给变量

Linux的官方Adobe Flash存储库是否已过时？

验证REST API参数

ggplot：对齐多个分面图-所有大小不同的分面

Mac OS X更新后的GRUB 2问题

通过 Git 在运行 Jenkins 作业时获取 ClassNotFoundException

带有错误“ where”条件的查询如何返回结果？

用日期数据透视表和日期顺序查询

VB.net将2条特定行导出到DataGridView

如何从视图一次更新多行（ASP.NET - Core）

Java Eclipse中的错误13，如何解决？

尝试反复更改屏幕上按钮的位置 - kotlin android studio

离子动态工具栏背景色

应用发明者仅从列表中选择一个随机项一次

当我尝试下载 StanfordNLP en 模型时，出现错误

python中的boto3文件上传

在同一Pushwoosh应用程序上Pushwoosh多个捆绑ID