我需要寻找每个客户的所有日期上连续的订单中间隔最长的两笔。
现在有一组叫Orders的表格。包含如下内容:
+-------+------------+----------+
|OrderID| OrderDate |CustomerID|
+-------+------------+----------+
| 101 | 2021-01-01 | 1 |
| 102 | 2021-01-10 | 1 |
| 103 | 2021-01-30 | 1 |
| 104 | 2020-11-01 | 2 |
| 105 | 2021-01-01 | 2 |
| 106 | 2021-01-22 | 2 |
| 107 | 2020-05-10 | 3 |
| 108 | 2020-07-10 | 3 |
| 109 | 2021-01-10 | 3 |
| 110 | 2021-01-13 | 4 |
| ... | .......... | .... |
+-------+------------+----------+
其中OrderID是订单编号,OrderDate是订单日期,CustomerID是客户。所有符合条件的CustomerID至少得有2个OrderID。所有不满足条件的用户不应该出现在最终结果里。
以上表格仅是参考。无法确定OrderDate和CustomerID的顺序。其实OrderID没有用,主要是关于OrderDate跟CustomerID。
其中包含4个客户(CustomerID),即 1,2,3,4。其中有3个跟1有关的OrderID,3个跟2有关的OrderID,3个跟3有关的OrderID,1个跟1有关的OrderID。因为客户4只有一笔订单,所以客户4不存在最大间隔时长,所以结果里的CustomerID不应该含有4。
关于连续的订单,它的意思是像101(2021-01-01)跟102(2021-01-10)是连续的,102(2021-01-10)跟103(2021-01-30)是连续的,但101跟103不是连续的,因为102的时间是在101跟103中间。所以最大日期减去最小日期得出的结果是不正确的。
此情况下,正确的结果应该是:
客户(CustomerID) | 最大时长(天) | 具体订单(OrderID) |
---|---|---|
1 | 20(天) | 订单号102(2021-01-10)订单号102(2021-01-30) |
2 | 61(天) | 订单号105(2021-01-10)订单号104(2021-01-30) |
3 | 122(天) | 订单号108(2020-07-10)订单号109(2021-01-10) |
上述表格返回的结果应该是:
+----------+----------+
|CustomerID| NumDays |
+----------+----------+
| 1 | 20 |
| 2 | 61 |
| 3 | 122 |
+----------+----------+
有很多东西是不能用的。比如说lag,limit,over。这是我看最常用的几种做法。但是它们一概都不能用。
我说实话对于这个问题完全不知道该怎么入手,希望能有人指导。
补充:所有不能使用的都在这个txt文件里了:forbidden.txt。很抱歉之前没有附上。
补充:所有不能用的如下:
不能使用的(forbidden.txt中的全部内容) |
---|
ABORT |
ABS |
ABSENT |
ABSOLUTE |
ACCESS |
ACCORDING |
ACOS |
ACTION |
ADA |
ADD |
ADMIN |
AFTER |
AGGREGATE |
ALLOCATE |
ALSO |
ALTER |
ALWAYS |
ANALYSE |
ANALYZE |
ARRAY |
ARRAY_AGG |
ARRAY_MAX_CARDINALITY |
ASENSITIVE |
ASIN |
ASSERTION |
ASSIGNMENT |
ASYMMETRIC |
ATAN |
ATOMIC |
ATTACH |
AUTHORIZATION |
BACKWARD |
BASE64 |
BEFORE |
BEGIN |
BEGIN_FRAME |
BEGIN_PARTITION |
BERNOULLI |
BETWEEN |
BIGINT |
BINARY |
BIT |
BIT_LENGTH |
BLOB |
BLOCKED |
BOM |
BOTH |
BREADTH |
CACHE |
CALL |
CALLED |
CARDINALITY |
CASCADE |
CASCADED |
CATALOG |
CATALOG_NAME |
CEIL |
CEILING |
CHAIN |
CHAINING |
CHARACTERISTICS |
CHARACTERS |
CHARACTER_LENGTH |
CHARACTER_SET_CATALOG |
CHARACTER_SET_NAME |
CHARACTER_SET_SCHEMA |
CHAR_LENGTH |
CHECK |
CHECKPOINT |
CLASS |
CLASSIFIER |
CLASS_ORIGIN |
CLOB |
CLOSE |
CLUSTER |
COALESCE |
COBOL |
COLLATE |
COLLATION |
COLLATION_CATALOG |
COLLATION_NAME |
COLLATION_SCHEMA |
COLLECT |
COMMAND_FUNCTION |
COMMAND_FUNCTION_CODE |
COMMENT |
COMMENTS |
COMMIT |
COMMITTED |
CONCURRENTLY |
CONDITION |
CONDITIONAL |
CONDITION_NUMBER |
CONFIGURATION |
CONFLICT |
CONNECT |
CONNECTION |
CONNECTION_NAME |
CONSTRAINT |
CONSTRAINTS |
CONSTRAINT_CATALOG |
CONSTRAINT_NAME |
CONSTRAINT_SCHEMA |
CONSTRUCTOR |
CONTAINS |
CONTENT |
CONTINUE |
CONTROL |
CONVERSION |
CONVERT |
COPY |
CORR |
CORRESPONDING |
COS |
COSH |
COST |
COVAR_POP |
COVAR_SAMP |
CREATE |
CROSS |
CSV |
CUBE |
CUME_DIST |
CURRENT |
CURRENT_CATALOG |
CURRENT_DATE |
CURRENT_DEFAULT_TRANSFORM_GROUP |
CURRENT_PATH |
CURRENT_ROLE |
CURRENT_ROW |
CURRENT_SCHEMA |
CURRENT_TIME |
CURRENT_TIMESTAMP |
CURRENT_TRANSFORM_GROUP_FOR_TYPE |
CURRENT_USER |
CURSOR |
CURSOR_NAME |
CYCLE |
DATA |
DATABASE |
DATALINK |
DATETIME_INTERVAL_CODE |
DATETIME_INTERVAL_PRECISION |
DAY |
DEALLOCATE |
DEC |
DECFLOAT |
DECLARE |
DEFAULT |
DEFAULTS |
DEFERRABLE |
DEFERRED |
DEFINE |
DEFINED |
DEFINER |
DELETE |
DELIMITER |
DELIMITERS |
DENSE_RANK |
DEPENDS |
DEPTH |
DEREF |
DERIVED |
DESCRIBE |
DESCRIPTOR |
DETACH |
DETERMINISTIC |
DIAGNOSTICS |
DICTIONARY |
DISABLE |
DISCARD |
DISCONNECT |
DISPATCH |
DLNEWCOPY |
DLPREVIOUSCOPY |
DLURLCOMPLETE |
DLURLCOMPLETEONLY |
DLURLCOMPLETEWRITE |
DLURLPATH |
DLURLPATHONLY |
DLURLPATHWRITE |
DLURLSCHEME |
DLURLSERVER |
DLVALUE |
DO |
DOCUMENT |
DOMAIN |
DOUBLE |
DROP |
DYNAMIC |
DYNAMIC_FUNCTION |
DYNAMIC_FUNCTION_CODE |
EACH |
ELEMENT |
EMPTY |
ENABLE |
ENCODING |
ENCRYPTED |
END_FRAME |
END_PARTITION |
ENFORCED |
ENUM |
EQUALS |
ERROR |
ESCAPE |
EVENT |
EVERY |
EXCEPTION |
EXCLUDE |
EXCLUDING |
EXCLUSIVE |
EXEC |
EXECUTE |
EXP |
EXPLAIN |
EXPRESSION |
EXTENSION |
EXTERNAL |
EXTRACT |
FAMILY |
FETCH |
FILE |
FILTER |
FINAL |
FINISH |
FIRST |
FIRST_VALUE |
FLAG |
FLOAT |
FLOOR |
FOLLOWING |
FOR |
FORCE |
FOREIGN |
FORMAT |
FORTRAN |
FORWARD |
FOUND |
FRAME_ROW |
FREE |
FREEZE |
FULFILL |
FULL |
FUNCTION |
FUNCTIONS |
FUSION |
GENERAL |
GENERATED |
GET |
GLOBAL |
GO |
GOTO |
GRANT |
GRANTED |
GREATEST |
GROUPING |
GROUPS |
HANDLER |
HEADER |
HEX |
HIERARCHY |
HOLD |
HOUR |
IDENTITY |
IF |
IGNORE |
ILIKE |
IMMEDIATE |
IMMEDIATELY |
IMMUTABLE |
IMPLEMENTATION |
IMPLICIT |
IMPORT |
INCLUDE |
INCLUDING |
INCREMENT |
INDENT |
INDEX |
INDEXES |
INDICATOR |
INHERIT |
INHERITS |
INITIAL |
INITIALLY |
INLINE |
INOUT |
INPUT |
INSENSITIVE |
INSERT |
INSTANCE |
INSTANTIABLE |
INSTEAD |
INTEGRITY |
INTERSECTION |
INTERVAL |
INTO |
INVOKER |
ISNULL |
ISOLATION |
JSON |
JSON_ARRAY |
JSON_ARRAYAGG |
JSON_EXISTS |
JSON_OBJECT |
JSON_OBJECTAGG |
JSON_QUERY |
JSON_TABLE |
JSON_TABLE_PRIMITIVE |
JSON_VALUE |
KEEP |
KEY |
KEYS |
KEY_MEMBER |
KEY_TYPE |
LABEL |
LAG |
LANGUAGE |
LARGE |
LAST |
LAST_VALUE |
LATERAL |
LEAD |
LEADING |
LEAKPROOF |
LEAST |
LEFT |
LENGTH |
LEVEL |
LIBRARY |
LIKE_REGEX |
LIMIT |
LINK |
LISTAGG |
LISTEN |
LOAD |
LOCAL |
LOCALTIME |
LOCALTIMESTAMP |
LOCATION |
LOCATOR |
LOCK |
LOCKED |
LOG |
LOG10 |
LOGGED |
LOWER |
MAP |
MAPPING |
MATCH |
MATCHED |
MATCHES |
MATCH_NUMBER |
MATCH_RECOGNIZE |
MATERIALIZED |
MAXVALUE |
MEASURES |
MEMBER |
MERGE |
MESSAGE_LENGTH |
MESSAGE_OCTET_LENGTH |
MESSAGE_TEXT |
METHOD |
MINUTE |
MINVALUE |
MODE |
MODIFIES |
MODULE |
MONTH |
MORE |
MOVE |
MULTISET |
MUMPS |
NAMESPACE |
NATIONAL |
NCHAR |
NCLOB |
NESTED |
NESTING |
NEW |
NEXT |
NFC |
NFD |
NFKC |
NFKD |
NIL |
NO |
NONE |
NORMALIZE |
NORMALIZED |
NOTHING |
NOTIFY |
NOTNULL |
NOWAIT |
NTH_VALUE |
NTILE |
NULL |
NULLABLE |
NULLIF |
NULLS |
NUMBER |
OBJECT |
OCCURRENCES_REGEX |
OCTETS |
OCTET_LENGTH |
OF |
OFF |
OFFSET |
OIDS |
OLD |
OMIT |
ONE |
ONLY |
OPEN |
OPERATOR |
OPTION |
OPTIONS |
ORDERING |
ORDINALITY |
OTHERS |
OUT |
OUTER |
OUTPUT |
OVER |
OVERFLOW |
OVERLAPS |
OVERLAY |
OVERRIDING |
OWNED |
OWNER |
PAD |
PARALLEL |
PARAMETER |
PARAMETER_MODE |
PARAMETER_NAME |
PARAMETER_ORDINAL_POSITION |
PARAMETER_SPECIFIC_CATALOG |
PARAMETER_SPECIFIC_NAME |
PARAMETER_SPECIFIC_SCHEMA |
PARSER |
PARTIAL |
PARTITION |
PASCAL |
PASS |
PASSING |
PASSTHROUGH |
PASSWORD |
PAST |
PATH |
PATTERN |
PER |
PERCENT |
PERCENTILE_CONT |
PERCENTILE_DISC |
PERCENT_RANK |
PERIOD |
PERMISSION |
PERMUTE |
PLACING |
PLAN |
PLANS |
PLI |
POLICY |
PORTION |
POSITION |
POSITION_REGEX |
POWER |
PRECEDES |
PRECEDING |
PRECISION |
PREPARE |
PREPARED |
PRESERVE |
PRIMARY |
PRIOR |
PRIVATE |
PRIVILEGES |
PROCEDURAL |
PROCEDURE |
PROCEDURES |
PROGRAM |
PRUNE |
PTF |
PUBLIC |
PUBLICATION |
QUOTE |
QUOTES |
RANGE |
RANK |
READ |
READS |
REASSIGN |
RECHECK |
RECOVERY |
RECURSIVE |
REF |
REFERENCES |
REFERENCING |
REFRESH |
REGR_AVGX |
REGR_AVGY |
REGR_COUNT |
REGR_INTERCEPT |
REGR_R2 |
REGR_SLOPE |
REGR_SXX |
REGR_SXY |
REGR_SYY |
REINDEX |
RELATIVE |
RELEASE |
RENAME |
REPEATABLE |
REPLACE |
REPLICA |
REQUIRING |
RESET |
RESPECT |
RESTART |
RESTORE |
RESTRICT |
RESULT |
RETURN |
RETURNED_CARDINALITY |
RETURNED_LENGTH |
RETURNED_OCTET_LENGTH |
RETURNED_SQLSTATE |
RETURNING |
RETURNS |
REVOKE |
RIGHT |
ROLE |
ROLLBACK |
ROLLUP |
ROUTINE |
ROUTINES |
ROUTINE_CATALOG |
ROUTINE_NAME |
ROUTINE_SCHEMA |
ROW_COUNT |
ROW_NUMBER |
RULE |
RUNNING |
SAVEPOINT |
SCALAR |
SCALE |
SCHEMA_NAME |
SCOPE |
SCOPE_CATALOG |
SCOPE_NAME |
SCOPE_SCHEMA |
SCROLL |
SEARCH |
SECOND |
SECTION |
SECURITY |
SEEK |
SELECTIVE |
SELF |
SENSITIVE |
SEQUENCE |
SEQUENCES |
SERIALIZABLE |
SERVER |
SERVER_NAME |
SESSION |
SESSION_USER |
SET |
SETOF |
SETS |
SHARE |
SHOW |
SIMILAR |
SIMPLE |
SIN |
SINH |
SIZE |
SKIP |
SNAPSHOT |
SOME |
SOURCE |
SPACE |
SPECIFIC |
SPECIFICTYPE |
SPECIFIC_NAME |
SQL |
SQLCODE |
SQLERROR |
SQLEXCEPTION |
SQLSTATE |
SQLWARNING |
SQRT |
STABLE |
STANDALONE |
START |
STATE |
STATEMENT |
STATIC |
STATISTICS |
STDDEV_POP |
STDDEV_SAMP |
STDIN |
STDOUT |
STORAGE |
STORED |
STRICT |
STRING |
STRIP |
STRUCTURE |
STYLE |
SUBCLASS_ORIGIN |
SUBMULTISET |
SUBSCRIPTION |
SUBSET |
SUBSTRING |
SUBSTRING_REGEX |
SUCCEEDS |
SUPPORT |
SYMMETRIC |
SYSID |
SYSTEM |
SYSTEM_TIME |
SYSTEM_USER |
TABLESAMPLE |
TABLESPACE |
TABLE_NAME |
TAN |
TANH |
TEMP |
TEMPLATE |
TEMPORARY |
TEXT |
THROUGH |
TIES |
TIME |
TIMESTAMP |
TIMEZONE_HOUR |
TIMEZONE_MINUTE |
TO |
TOKEN |
TOP_LEVEL_COUNT |
TRAILING |
TRANSACTION |
TRANSACTIONS_COMMITTED |
TRANSACTIONS_ROLLED_BACK |
TRANSACTION_ACTIVE |
TRANSFORM |
TRANSFORMS |
TRANSLATE |
TRANSLATE_REGEX |
TRANSLATION |
TREAT |
TRIGGER |
TRIGGER_CATALOG |
TRIGGER_NAME |
TRIGGER_SCHEMA |
TRIM |
TRIM_ARRAY |
TRUNCATE |
TRUSTED |
UESCAPE |
UNBOUNDED |
UNCOMMITTED |
UNCONDITIONAL |
UNDER |
UNENCRYPTED |
UNIQUE |
UNKNOWN |
UNLINK |
UNLISTEN |
UNLOGGED |
UNMATCHED |
UNNAMED |
UNNEST |
UNTIL |
UNTYPED |
UPDATE |
UPPER |
URI |
USAGE |
USER |
USER_DEFINED_TYPE_CATALOG |
USER_DEFINED_TYPE_CODE |
USER_DEFINED_TYPE_NAME |
USER_DEFINED_TYPE_SCHEMA |
USING |
UTF16 |
UTF32 |
UTF8 |
VACUUM |
VALID |
VALIDATE |
VALIDATOR |
VALUE_OF |
VARBINARY |
VARIADIC |
VAR_POP |
VAR_SAMP |
VERBOSE |
VERSION |
VERSIONING |
VIEW |
VIEWS |
VOLATILE |
WHENEVER |
WHITESPACE |
WIDTH_BUCKET |
WINDOW |
WITH |
WITHIN |
WITHOUT |
WORK |
WRAPPER |
WRITE |
ZONE |