无法在sql中将日期放在一列中，将度量放在另一列中

bxpogfeg  于 2021-07-26  发布在  Java
关注(0)|答案(2)|浏览(389)

问题：对于给定的t天，我需要为卖家id“123”创建61天的准时交货（otd）指标。otd是指在t之前30天内创建的包裹按时交付的速率。
我们不仅想知道今天的otd，还想知道过去60天里每个日期的otd。

  1. CREATE TABLE `packages` (
  2.   `id` int NOT NULL,
  3.   `created_at` timestamp NULL DEFAULT NULL,
  4.   `seller_id` int DEFAULT NULL,
  5.   `promise_date` date DEFAULT NULL,
  6.   `delivered_at` timestamp NULL DEFAULT NULL,
  7.   PRIMARY KEY (`id`)
  8. );
  9. INSERT INTO `packages` (`id`, `created_at`, `seller_id`, `promise_date`, `delivered_at`) VALUES
  10. ('1', '2020-04-15 06:26:28', '246', '2020-04-20', '2020-04-20 07:47:07'),
  11. ('2', '2020-05-02 06:19:13', '123', '2020-05-06', '2020-05-07 07:01:26'),
  12. ('3', '2020-07-05 08:47:17', '789', '2020-07-08', '2020-07-09 08:22:08'),
  13. ('4', '2020-03-25 08:12:35', '234', '2020-03-30', '2020-03-30 08:49:50'),
  14. ('5', '2020-07-01 07:51:33', '789', '2020-07-06', '2020-07-06 08:34:48'),
  15. ('6', '2020-03-01 08:11:31', '123', '2020-03-04', '2020-03-04 06:00:14'),
  16. ('7', '2020-05-20 07:38:14', '246', '2020-05-25', '2020-05-25 08:13:51'),
  17. ('8', '2020-04-14 07:30:19', '123', '2020-04-17', '2020-04-17 07:46:55'),
  18. ('9', '2020-02-10 08:53:00', '234', '2020-02-13', '2020-02-14 06:45:57'),
  19. ('10', '2020-01-21 07:14:56', '246', '2020-01-24', '2020-01-25 08:03:04'),
  20. ('11', '2020-07-03 08:05:11', '123', '2020-07-08', '2020-07-09 08:30:56'),
  21. ('12', '2020-05-09 06:18:31', '789', '2020-05-13', '2020-05-13 08:38:55'),
  22. ('13', '2020-02-13 08:11:10', '123', '2020-02-18', '2020-02-18 07:48:52'),
  23. ('14', '2020-04-28 08:25:28', '789', '2020-05-01', '2020-05-02 06:06:32'),
  24. ('15', '2020-06-02 07:28:52', '234', '2020-06-05', '2020-06-06 07:29:43'),
  25. ('16', '2020-05-04 08:39:33', '123', '2020-05-07', '2020-05-08 06:33:14'),
  26. ('17', '2020-07-26 08:18:30', '789', '2020-07-29', '2020-07-30 07:28:53'),
  27. ('18', '2020-02-25 08:37:42', '234', '2020-02-28', '2020-02-29 06:05:23'),
  28. ('19', '2020-02-03 06:55:39', '234', '2020-02-06', '2020-02-07 07:18:28'),
  29. ('20', '2020-03-07 08:20:44', '246', '2020-03-11', '2020-03-11 08:11:45'),
  30. ('21', '2020-03-11 07:19:47', '789', '2020-03-16', '2020-03-16 06:55:46'),
  31. ('22', '2020-06-24 08:18:56', '789', '2020-06-29', '2020-06-29 08:47:59'),
  32. ('23', '2020-02-25 07:24:19', '123', '2020-02-28', '2020-02-28 06:54:57'),
  33. ('24', '2020-07-12 07:51:52', '789', '2020-07-15', '2020-07-16 07:36:21'),
  34. ('25', '2020-01-26 07:44:59', '234', '2020-01-29', '2020-01-29 08:52:24'),
  35. ('26', '2020-02-07 06:09:24', '246', '2020-02-12', '2020-02-13 08:16:37'),
  36. ('27', '2020-03-11 08:34:57', '123', '2020-03-16', '2020-03-17 08:33:47'),
  37. ('28', '2020-02-24 08:15:41', '789', '2020-02-27', '2020-02-27 06:19:59'),
  38. ('29', '2020-02-02 06:45:36', '123', '2020-02-05', '2020-02-06 06:22:25'),
  39. ('30', '2020-02-10 06:51:48', '123', '2020-02-13', '2020-02-13 06:45:07'),
  40. ('31', '2020-03-27 07:11:58', '789', '2020-04-01', '2020-04-02 08:55:56'),
  41. ('32', '2020-05-31 07:10:05', '246', '2020-06-03', '2020-06-03 08:56:47'),
  42. ('33', '2020-06-28 06:14:19', '789', '2020-07-01', '2020-07-02 06:35:18'),
  43. ('34', '2020-07-08 08:30:12', '789', '2020-07-13', '2020-07-14 06:06:09'),
  44. ('35', '2020-05-13 08:13:34', '123', '2020-05-18', '2020-05-18 08:24:42'),
  45. ('36', '2020-04-19 08:13:38', '246', '2020-04-22', '2020-04-22 07:32:14'),
  46. ('37', '2020-03-02 06:57:32', '234', '2020-03-05', '2020-03-05 07:16:05'),
  47. ('38', '2020-05-22 08:49:51', '246', '2020-05-27', '2020-05-27 06:47:41'),
  48. ('39', '2020-02-27 08:18:26', '123', '2020-03-03', '2020-03-03 06:32:56'),
  49. ('40', '2020-02-17 07:10:24', '246', '2020-02-20', '2020-02-21 06:06:26'),
  50. ('41', '2020-06-25 08:29:32', '234', '2020-06-30', '2020-06-30 07:37:07'),
  51. ('42', '2020-03-02 08:07:57', '234', '2020-03-05', '2020-03-05 08:41:13'),
  52. ('43', '2020-06-18 06:44:38', '234', '2020-06-23', '2020-06-23 06:11:26'),
  53. ('44', '2020-07-15 08:22:49', '246', '2020-07-20', '2020-07-20 08:34:28'),
  54. ('45', '2020-07-07 07:54:10', '123', '2020-07-10', '2020-07-10 07:50:24'),
  55. ('46', '2020-07-17 07:43:08', '123', '2020-07-22', '2020-07-22 06:33:22'),
  56. ('47', '2020-04-01 08:24:20', '234', '2020-04-06', '2020-04-06 06:12:55'),
  57. ('48', '2020-05-14 08:49:10', '123', '2020-05-19', '2020-05-20 06:53:50'),
  58. ('49', '2020-06-11 08:20:35', '246', '2020-06-16', '2020-06-16 06:21:10'),
  59. ('50', '2020-06-24 06:39:29', '789', '2020-06-29', '2020-06-30 06:03:48'),
  60. ('51', '2020-02-29 06:43:01', '246', '2020-03-04', '2020-03-05 07:57:51'),
  61. ('52', '2020-07-17 08:23:46', '246', '2020-07-22', '2020-07-22 07:49:01'),
  62. ('53', '2020-03-27 07:45:10', '123', '2020-04-01', '2020-04-02 06:43:34'),
  63. ('54', '2020-04-28 06:39:55', '246', '2020-05-01', '2020-05-01 08:59:08'),
  64. ('55', '2020-05-21 07:16:03', '789', '2020-05-26', '2020-05-26 06:29:53'),
  65. ('56', '2020-02-10 08:22:04', '246', '2020-02-13', '2020-02-14 06:24:17'),
  66. ('57', '2020-02-02 08:04:26', '234', '2020-02-05', '2020-02-05 08:59:43'),
  67. ('58', '2020-03-02 08:21:53', '246', '2020-03-05', '2020-03-06 08:45:36'),
  68. ('59', '2020-02-19 08:37:15', '123', '2020-02-24', '2020-02-24 08:24:44'),
  69. ('60', '2020-06-16 08:51:24', '234', '2020-06-19', '2020-06-20 08:25:14'),
  70. ('61', '2020-07-11 07:37:15', '234', '2020-07-15', '2020-07-15 07:03:13'),
  71. ('62', '2020-06-15 07:56:39', '123', '2020-06-18', '2020-06-19 07:11:16'),
  72. ('63', '2020-03-06 07:21:52', '123', '2020-03-11', '2020-03-11 07:46:48'),
  73. ('64', '2020-06-03 06:43:50', '789', '2020-06-08', '2020-06-09 07:40:17'),
  74. ('65', '2020-01-20 06:28:47', '234', '2020-01-23', '2020-01-24 08:34:05'),
  75. ('66', '2020-04-02 08:04:41', '123', '2020-04-07', '2020-04-08 08:56:45'),
  76. ('67', '2020-03-04 06:05:57', '789', '2020-03-09', '2020-03-10 06:26:56'),
  77. ('68', '2020-07-04 06:47:46', '246', '2020-07-08', '2020-07-09 06:53:02'),
  78. ('69', '2020-02-25 06:47:09', '246', '2020-02-28', '2020-02-28 07:55:25'),
  79. ('70', '2020-02-04 07:17:28', '123', '2020-02-07', '2020-02-07 08:07:54'),
  80. ('71', '2020-06-15 07:18:16', '789', '2020-06-18', '2020-06-19 06:02:08'),
  81. ('72', '2020-07-09 06:32:34', '234', '2020-07-14', '2020-07-14 08:15:02'),
  82. ('73', '2020-05-21 06:12:52', '789', '2020-05-26', '2020-05-27 07:39:20'),
  83. ('74', '2020-05-24 06:38:49', '789', '2020-05-27', '2020-05-27 06:51:35'),
  84. ('75', '2020-02-27 06:31:02', '123', '2020-03-03', '2020-03-03 08:56:26'),
  85. ('76', '2020-07-02 08:55:00', '123', '2020-07-07', '2020-07-07 07:42:16'),
  86. ('77', '2020-06-30 06:52:27', '246', '2020-07-03', '2020-07-03 07:43:20'),
  87. ('78', '2020-04-25 08:08:14', '246', '2020-04-29', '2020-04-29 07:21:23'),
  88. ('79', '2020-06-24 08:34:43', '234', '2020-06-29', '2020-06-30 06:43:59'),
  89. ('80', '2020-05-13 08:59:11', '246', '2020-05-18', '2020-05-18 07:19:06'),
  90. ('81', '2020-02-21 07:14:16', '789', '2020-02-26', '2020-02-27 07:10:39'),
  91. ('82', '2020-06-04 08:43:13', '789', '2020-06-09', '2020-06-09 07:24:28'),
  92. ('83', '2020-07-04 07:14:42', '234', '2020-07-08', '2020-07-09 07:45:59'),
  93. ('84', '2020-05-24 08:17:00', '246', '2020-05-27', '2020-05-27 06:31:15'),
  94. ('85', '2020-03-07 07:43:27', '123', '2020-03-11', '2020-03-12 08:39:45');
  96. create table dates(
  97. fulldate date);
  99. INSERT INTO `dates` (`fulldate`) VALUES
  100. ('2020-08-01'),
  101. ('2020-07-31'),
  102. ('2020-07-30'),
  103. ('2020-07-29'),
  104. ('2020-07-28'),
  105. ('2020-07-27'),
  106. ('2020-07-26'),
  107. ('2020-07-25'),
  108. ('2020-07-24'),
  109. ('2020-07-23'),
  110. ('2020-07-22'),
  111. ('2020-07-21'),
  112. ('2020-07-20'),
  113. ('2020-07-19'),
  114. ('2020-07-18'),
  115. ('2020-07-17'),
  116. ('2020-07-16'),
  117. ('2020-07-15'),
  118. ('2020-07-14'),
  119. ('2020-07-13'),
  120. ('2020-07-12'),
  121. ('2020-07-11'),
  122. ('2020-07-10'),
  123. ('2020-07-09'),
  124. ('2020-07-08'),
  125. ('2020-07-07'),
  126. ('2020-07-06'),
  127. ('2020-07-05'),
  128. ('2020-07-04'),
  129. ('2020-07-03'),
  130. ('2020-07-02'),
  131. ('2020-07-01'),
  132. ('2020-06-30'),
  133. ('2020-06-29'),
  134. ('2020-06-28'),
  135. ('2020-06-27'),
  136. ('2020-06-26'),
  137. ('2020-06-25'),
  138. ('2020-06-24'),
  139. ('2020-06-23'),
  140. ('2020-06-22'),
  141. ('2020-06-21'),
  142. ('2020-06-20'),
  143. ('2020-06-19'),
  144. ('2020-06-18'),
  145. ('2020-06-17'),
  146. ('2020-06-16'),
  147. ('2020-06-15'),
  148. ('2020-06-14'),
  149. ('2020-06-13'),
  150. ('2020-06-12'),
  151. ('2020-06-11'),
  152. ('2020-06-10'),
  153. ('2020-06-09'),
  154. ('2020-06-08'),
  155. ('2020-06-07'),
  156. ('2020-06-06'),
  157. ('2020-06-05'),
  158. ('2020-06-04'),
  159. ('2020-06-03'),
  160. ('2020-06-02');

期望输出：

  1. date       | on_time_delivery_ratio
  3. 2020-07-31 | 0.75  
  4. 2020-07-30 | 0.69  
  5. 2020-07-29 | 0.68  
  6. 2020-07-28 | 0.80  
  7. 2020-07-27 | 0.79  
  8. 2020-07-26 | 0.78  
  9. 2020-07-25 | 0.69  
  10. 2020-07-24 | 0.72

我所做的：
我已经能够为当前日期创建一个度量。

  1. SELECT curdate(),sum(case when promise_date=date(delivered_at) then 1 else 0 end)/count(*)*100 
  2.  "On-Time Delivery Rate (%)"
  3. from packages p 
  4. where p.seller_id=123 and date(created_at) between DATE_SUB(curdate(),interval 30 day) and 
  5. DATE_SUB(curdate(),interval 1 day);

我需要帮助的地方：
我不需要使用curdate（），而是需要dates表中的日期作为所需输出的第一列以及这些日期的度量。
sql语句fiddle:http：//sqlfiddle.com/#！9/b665ca1/2号

sqlmysqlJoinDateMetrics

2条答案

按热度按时间
q5lcpyga

q5lcpyga1#

小提琴：http://sqlfiddle.com/#!9/b665ca1/7（基于http://sqlfiddle.com/#!9/b665ca1/2）

  1. SELECT 
  2.    delivered_at,
  3.    sum(case when promise_date=date(delivered_at) then 1 else 0 end)/count(*)  "On-Time Delivery Rate (%)"
  4. from packages p 
  5. where p.seller_id=123 
  6.   and date(delivered_at) between DATE_SUB(curdate(),interval 30 day) and DATE_SUB(curdate(),interval 1 day)
  7. group by delivered_at;

输出：

  1. delivered_at    On-Time Delivery Rate (%)
  2. 2020-07-07T07:42:16Z    1
  3. 2020-07-09T08:30:56Z    0
  4. 2020-07-10T07:50:24Z    1
  5. 2020-07-22T06:33:22Z    1

编辑：选择最近61天

  1. SELECT date.d,sum(case when promise_date=date(delivered_at) then 1 else 0 end)/count(*)*100 
  2.  "On-Time Delivery Rate (%)"
  3. from date 
  4. CROSS JOIN packages p 
  5. where p.seller_id=123 
  6.   and date(created_at) between DATE_SUB(date.d,interval 30 day) and DATE_SUB(date.d,interval 1 day)
  7.   and date.d >= DATE_SUB(curdate(),interval 61 day)
  8. GROUP BY date.d
  9. ORDER BY date.d;

我换了衣服 curdate() 从查询到起息日。 select * from date 应该给出所有日期，至少从过去61天开始。

展开查看全部
赞(0）分享  回复(0）举报  2021-07-26
kpbpu008

kpbpu0082#

你想得到所有日期的otd百分比吗 dates table 从何处获取相应日期的指标。因此，百分比计算为：（（过去30天至该日期前一天的准时交货数量）/（过去30天至该日期前一天的总交货数量））*100；
因此，如果以日期2020-06-02为例，在2020-05-02到2020-06-01之间总共发生了3次交货，其中1次准时交货，因此otd百分比=33.3
据我所知，以下是您可能正在寻找的解决方案：
请检查sql fiddle：

  1. SELECT d.fulldate,sum(case when promise_date = date(delivered_at) 
  2.                       and p.seller_id = 123 and date(created_at) between DATE_SUB(d.fulldate,interval 30 day) and 
  3. DATE_SUB(d.fulldate,interval 1 day) then 1 else 0 end)/sum(case when date(created_at) between DATE_SUB(d.fulldate,interval 30 day) and 
  4. DATE_SUB(d.fulldate,interval 1 day) and p.seller_id = 123 then 1 else 0 end)*100
  5. "On-Time Delivery Rate (%)"
  6. from packages p, dates d group by d.fulldate;
  7. where p.seller_id=123 and date(created_at) between DATE_SUB(d.fulldate,interval 30 day) and 
  8. DATE_SUB(d.fulldate,interval 1 day);

输出：
下面是部分输出：（对于完整输出，您可以检查查询上方给定的fiddle）

  1. fulldate    On-Time Delivery Rate (%)
  2. 2020-06-02  33.3333
  3. 2020-06-03  33.3333
  4. 2020-06-04  50
  5. 2020-06-05  50
  6. 2020-06-06  50
  7. 2020-06-07  50
展开查看全部
赞(0）分享  回复(0）举报  2021-07-26
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com