我两天来一直在想这个问题。
首先，让我说，我是一个新的发展，所以它可能是一个简单的查询，但我只是不能搞清楚。我在这个网站上搜索过，试图找到一个解决方案，但如果有一个类似的，我就是不明白。
我要做的是从数据库中获取一些数字。我需要的用户总数，不同的用户，收到的信息数量和发送的信息数量，以及电子邮件收集，电话号码收集和用户订阅。所有这些数据都必须与特定的帐户id相关联。

select COUNT(r.uid) as total_users,COUNT(DISTINCT(r.uid)) as unique_users,  COUNT(r.message) as messages, COUNT(s.message) as messages_sent,
count(ps.email) as emails_collected, 
count(ps.phone_number) as phone_nums_collected,
count(ps.added) as new_users
from conversation_facebook_page_received r
INNER JOIN conversation_facebook_page_sent s ON r.account_id = s.account_id
INNER JOIN conversation_facebook_page_subscribers ps on r.account_id = ps.account_id
WHERE DATE(FROM_UNIXTIME(r.received_time / 1000)) > "2018-01-01 00:00:00" AND r.account_id =616646015120462;

样本数据：

CREATE TABLE `conversation_facebook_page_received` (
  `account_id` bigint(20) unsigned NOT NULL,
  `uid` bigint(20) unsigned NOT NULL,
  `msg_timestamp` bigint(20) unsigned NOT NULL,
  `received_time` bigint(20) unsigned DEFAULT NULL,
  `flow_id` int(10) unsigned DEFAULT NULL,
  `step_id` tinyint(3) unsigned DEFAULT NULL,
  `message_id` varchar(100) DEFAULT NULL,
  `message_type` varchar(20) DEFAULT NULL,
  `message` text,
  PRIMARY KEY (`account_id`,`uid`,`msg_timestamp`),
  KEY `flow_id` (`flow_id`),
  KEY `account_id` (`account_id`,`uid`,`received_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `conversation_facebook_page_received` (`account_id`, `uid`, `msg_timestamp`, `received_time`, `flow_id`, `step_id`, `message_id`, `message_type`, `message`)
VALUES
    (616646015120462,2503924196379227,1584982039136,1584982040232,NULL,NULL,NULL,'postback','Get Started'),
    (616646015120462,2503924196379227,1584982386400,1584982387189,NULL,NULL,'m_9X57kg_kRU4_WNXOFvt2QMglfcLimk7CpqvcjtCEbgZYdyYCx-Q5Z4OD_0XmRQzDCshgWcqGBHu7gt_aEhZRmw','text','thank you Bill you are doing the right thing. thank you.  please please lets stop these public gatherings NOW like Barnry Fife would say lets nip it in the bud  so far we been doing pretty good. lets step up our protection this is a must to defeat and win this war with the invisible enemy. i am seeing some school students treating this like  a vacation. they think they are invincible i guess as we did at that age. but somehow this needs enforcing maybe curfew will handle it.  lets stand proud and be protective please'),
    (85825440048,2503924196379227,1584983030585,1584983031341,NULL,NULL,NULL,'postback','Where are you located?'),
    (85825440048,2503924196379227,1584983047452,1584983048370,NULL,NULL,'m_xmK7lKY_86EIlS8dIQuRWMglfcLimk7CpqvcjtCEbgYaKd5ju8Nxscj78YjQWMUMFQkDZ161flNUZkLzZcDLgQ','text','moultrie ga'),
    (85825440048,2612891932156066,1586146320864,1586146321550,NULL,NULL,'m_4SNxwBOETFo3AEkxrRI_aNp0bwZ0kZj3tHm2Pjjq-Pk0SlZiIJrBlXMzdWPdpF80V2Bxw5rn9W34Yihs5oUUrA','text','could be useful'),
    (616646015120462,2792591144203564,1586896951790,1586896953965,NULL,NULL,NULL,'postback','Get Started'),
    (85825440048,3024889994213410,1587234572314,1587234572945,NULL,NULL,NULL,'postback','Get Started'),
    (85825440048,3278699712163817,1590010576412,1590010577520,NULL,NULL,NULL,'postback','Get Started'),
    (85825440048,3300581309974788,1587015278753,1587015279577,NULL,NULL,NULL,'postback','Get Started');

    CREATE TABLE `conversation_facebook_page_sent` (
  `account_id` bigint(20) unsigned NOT NULL,
  `uid` bigint(20) unsigned DEFAULT NULL,
  `flow_id` int(10) unsigned DEFAULT NULL,
  `step_id` tinyint(3) unsigned DEFAULT NULL,
  `send_time` bigint(20) unsigned DEFAULT NULL,
  `delivery_time` bigint(20) unsigned DEFAULT NULL,
  `read_time` bigint(20) unsigned DEFAULT NULL,
  `click_time` bigint(20) unsigned DEFAULT NULL,
  `message_id` varchar(100) DEFAULT NULL,
  `messaging_type` varchar(11) DEFAULT NULL,
  `tag` varchar(24) DEFAULT NULL,
  `message` text,
  KEY `flow_id` (`flow_id`),
  KEY `message_id` (`message_id`,`delivery_time`),
  KEY `account_id` (`account_id`,`uid`,`send_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `conversation_facebook_page_sent` (`account_id`, `uid`, `flow_id`, `step_id`, `send_time`, `delivery_time`, `read_time`, `click_time`, `message_id`, `messaging_type`, `tag`, `message`)
VALUES
  (616646015120462,2560048887396840,311,12,1583776598792,NULL,NULL,NULL,'k9Y48rBJ00rEt3pQB6WKnYcxhm_DmqdBwkxj2rJUQw1qrWJ2mTK676jH4oKNxSYR_SaK9caNXgXmebCkMGWucA','RESPONSE',NULL,'{\"attachment\":{\"type\":\"template\",\"payload\":{\"template_type\":\"button\",\"text\":\"Enter the BarkyPark  Sweepstakes and answer a simple 1 question survey to win a prize for you and your dog!\\nAre you ready to get started?\",\"buttons\":[{\"type\":\"postback\",\"title\":\"Get Me Started!\",\"payload\":\"254f28383a5e5d495561414039273859\"}]}}}'),
  (135613753250298,2681220635324690,382,2,1583778926385,1585072862424,NULL,NULL,'3lVOWAVsPtPrezfsfVmp4KLNDO4MLhlMAzKDQb7H-NqsvIZNa218b4nbGFUW-UfIcmYYXRTpZYsYpYJ0q68P8A','RESPONSE',NULL,'{\"text\":\"Just enter or tap your email address below to join our online \'ohana & receive discounts, secret offers and free stuff!\",\"quick_replies\":[{\"content_type\":\"user_email\"}]}'),
  (135613753250298,2681220635324690,382,5,1583778959043,1585072862424,NULL,NULL,'RvG5e69Dz6tamKYfRS6G5KLNDO4MLhlMAzKDQb7H-Nq8BA4sTK9Fooj1-hKb57SGSa7kYfpHEq68lP4Y4f6iYQ','RESPONSE',NULL,'{\"text\":\"You\'re all set! Go to your inbox to find your secret 15% coupon code! Mahalo for your support and welcome to the \'ohana!\"}'),
  (616646015120462,3452199664850501,382,2,1583780413556,NULL,NULL,NULL,'TyMsC7YuxGm1rj7Bsm_wCldXcpdYq_M6kRBtcWnLwNnv3xNUd7-WC0ANAebVwnD14ld3vagpMiSmoKgbAwvPMw','RESPONSE',NULL,'{\"text\":\"Just enter or tap your email address below to join our online \'ohana & receive discounts, secret offers and free stuff!\",\"quick_replies\":[{\"content_type\":\"user_email\"}]}'),
  (135613753250298,3452199664850501,382,5,1583780425764,NULL,NULL,NULL,'fArsI5l7Kfi_ys7KteuCcldXcpdYq_M6kRBtcWnLwNkQ58PZesvFKfYoaIpXoIH6qQR-RmVmd4LZ8Sm3KYHjOw','RESPONSE',NULL,'{\"text\":\"You\'re all set! Go to your inbox to find your secret 15% coupon code! Mahalo for your support and welcome to the \'ohana!\"}'),
  (616646015120462,2916614285069775,382,2,1583782640116,NULL,NULL,NULL,'XqLkq7eq2-eorH0YUNOb1PQyrq48TiSkKb3IzaXtI_MoMC2zRu1Y8g7wdX21vyobi8Xqs5UxRMCwS4HsPzjpPg','RESPONSE',NULL,'{\"text\":\"Just enter or tap your email address below to join our online \'ohana & receive discounts, secret offers and free stuff!\",\"quick_replies\":[{\"content_type\":\"user_email\"}]}'),
  (135613753250298,2916614285069775,382,5,1583782662572,NULL,NULL,NULL,'5sfoANj_a6wY9r6uBcGq9PQyrq48TiSkKb3IzaXtI_NnU8xY5WCqGUxzwgPGAbNCRjeXCFaS1ROO89rPSARkQw','RESPONSE',NULL,'{\"text\":\"You\'re all set! Go to your inbox to find your secret 15% coupon code! Mahalo for your support and welcome to the \'ohana!\"}'),
  (135613753250298,2573340896108684,382,2,1583783558954,NULL,NULL,NULL,'2OtlgFNwcp7kXDD2fOneT9LlZVZ3bAkwnrR1oPc20zX1psaItIi2yt9HaXYXFQJgC_eZ68Rm-0uKPtW-cQKLSA','RESPONSE',NULL,'{\"text\":\"Just enter or tap your email address below to join our online \'ohana & receive discounts, secret offers and free stuff!\",\"quick_replies\":[{\"content_type\":\"user_email\"}]}'),
  (135613753250298,2573340896108684,382,5,1583783573525,NULL,NULL,NULL,'bdqtU1VsVI6RmDgshnN-dtLlZVZ3bAkwnrR1oPc20zWrEU-LOyW7WnoraCIlC6UgiMP9Nces8R1UEjJk-E4J5g','RESPONSE',NULL,'{\"text\":\"You\'re all set! Go to your inbox to find your secret 15% coupon code! Mahalo for your support and welcome to the \'ohana!\"}');

  CREATE TABLE `conversation_facebook_page_subscribers` (
  `account_id` bigint(20) unsigned NOT NULL,
  `uid` bigint(20) unsigned NOT NULL,
  `name` varchar(120) DEFAULT NULL,
  `first_name` varchar(60) DEFAULT NULL,
  `last_name` varchar(60) DEFAULT NULL,
  `email` varchar(64) DEFAULT NULL,
  `phone_number` varchar(30) DEFAULT NULL,
  `profile_pic` varchar(255) DEFAULT NULL,
  `subscribed` tinyint(1) unsigned DEFAULT '1',
  `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `last_contact` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `unsubscribe` datetime DEFAULT NULL,
  PRIMARY KEY (`account_id`,`uid`),
  KEY `account_id` (`account_id`,`last_contact`,`subscribed`,`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `conversation_facebook_page_subscribers` (`account_id`, `uid`, `name`, `first_name`, `last_name`, `email`, `phone_number`, `profile_pic`, `subscribed`, `added`, `last_contact`, `unsubscribe`)
VALUES
  (616646015120462,1162227020568661,'My Name','My','Name',NULL,NULL,NULL,1,'2019-10-06 11:04:35','2019-10-06 11:34:45',NULL),
  (616646015120462,1964363690254660,'Some Name','Some','Name',NULL,NULL,NULL,1,'2019-03-26 21:53:56','2019-05-01 15:25:34',NULL),
  (616646015120462,2118450464865475,'John Doe','John','Doe',NULL,NULL,NULL,1,'2019-01-16 20:04:25','2019-05-01 15:25:34',NULL),
  (85825440048,2170134986379371,'Andrew Stokes','Andrew','Stokes',NULL,NULL,NULL,1,'2019-01-22 02:22:41','2019-05-01 15:25:34',NULL),
  (616646015120462,2198986756859748,'Frank Kroger','Frank','Kroger',NULL,NULL,NULL,1,'2019-05-08 00:47:13','2019-12-24 06:45:09',NULL),
  (85825440048,2274994739293368,'Paul Briggs','Paul','Briggs',NULL,NULL,NULL,1,'2019-10-16 22:34:28','2019-10-16 22:37:08',NULL),
  (616646015120462,2485007991552080,'Pawan Bhamu','Pawan','Bhamu',NULL,NULL,NULL,1,'2019-05-20 22:42:49','2019-05-20 23:07:53',NULL),
  (85825440048,2503924196379227,'Scott Pettyjohn','Scott','Pettyjohn',NULL,NULL,NULL,1,'2020-03-23 16:47:20','2020-03-23 17:04:08',NULL),
  (85825440048,2530973936973839,'Firas M. Alameh','Firas','Alameh',NULL,NULL,NULL,1,'2019-03-11 07:21:03','2019-05-01 15:25:34',NULL);

此外，我还在db-fiddle.com上创建了一个小提琴。
小提琴的网址是https://www.db-fiddle.com/f/gqjbr8u3ziik4rxryghpvf/1. 在fiddle中，我只有10行，但是如果运行fiddle，它会返回45个总用户、2个唯一用户、45条消息等等，这些数字不可能是正确的。有人能告诉我我做错了什么吗？我想理解sql，但显然我不理解它，否则我就不会得到如此扭曲的数字。事先谢谢你能给我的任何帮助。请向我解释我做错了什么。