数据库逻辑模型

数据库关系模型（数据库逻辑模型）是将数据概念模型转换为所使用的数据库管理系统（DBMS）支持的数据库逻辑结构，即将E-R图表示成关系数据库模式。数据库逻辑设计的结果不是唯一的，需利用规范化理论对数据库结构进行优化。

在关系模型中，数据库的逻辑结构是一张二维表。在数据库中，满足下列条件的二维表称为关系模型：

1）每列中的分量是类型相同的数据；

2）列的顺序可以是任意的；

3）行的顺序可以是任意的；

4）表中的分量是不可再分割的最小数据项，即表中不允许有子表；

5）表中的任意两行不能完全相同。

由此可见，有序的航空物探测量剖面数据不满足数据库关系模型条件第3条“行的顺序可以是任意的”，因此，不能简单地直接利用关系数据库（如Oracle，SQL Server，Sybase等）来管理剖面数据，需将数据在数据库中的存储方式改为大字段存储，确保不因数据库数据的增加和删除等 *** 作改变剖面数据有序特性。

一、大字段存储

（一）大字段存储技术

大字段LOB（Large Object）技术是Oracle专门用于存放处理大对象类型数据（如多媒体材料、影像资料、文档资料等）的数据管理技术。LOB包括内部的和外部的两种类型。内部LOB又分CLOB（字符型）、BLOB（二进制型）等3种数据类型，其数据存储在数据库中，并且支持事务 *** 作；外部LOB只有BFILE类型，其数据存储在 *** 作系统中，并且不支持事务 *** 作。LOB存放数据的长度最大可以达到4G字节，并且空值列（没有存放数据）不占空间（图2-6）。

图2-6 大字段存储示意图

由于外部LOB存放在 *** 作系统文件中，其安全性比内部LOB差一些。此外，大字段的存储支持事务 *** 作（批量提交和回滚等），而外部LOB不支持事务 *** 作。所以，航空物探测量剖面数据采用BLOB来存储。对于BLOB类型，如果数据量小于4000字节，数据库通常采用行内存储，而数据量大于4000字节采用行外存储。分析航空物探测量剖面数据，每个场值数据占4个字节（单精度），目前航磁数据采样率为10次/s，4000字节只能存储100 s数据；一般情况下航空物探测量每条测线飞行时间至少在10 min以上，每条测线数据量远远大于4000字节。所以，航空物探测量剖面数据采用行外存储方式，即大字段列指定“Disable Storage In Row”的存储参数。

由于大字段类型长度可变，最大可到4G。假设测线飞行时间为T，场值采样率为n次/s，测线场值数据量为4Tn，所以有4Tn≤4G。单条测线飞行时间T不会超过10 h（36000 s，航空物探测量1架次至少飞行1个往返2条测线），则场值的采样率n≤4G/4T=4×1024×1024×1024/4×36000次/s=29826次/s。采用大字段来存储测量数据，不仅能够减少数据表的记录数，提高查询效率，而且使得采样率的扩展不受限制。

（二）大字段存储技术应用

由于航空物探数据的数据量较大，现有的航磁测量数据按基准点方式（点存储）存储可达几亿个数据记录。若按磁场数据采样点存储方式（简称“场值存储方式”），则记录条数=（磁场数据采样率/坐标采样率）点存储方式的记录数，达几十亿条数据记录，且随着数据采样率的扩展、测点的加密，航空物探测量数据量随着时间的推移呈现快速增长之势。显然，如果采用常规的表结构来存储，势必造成数据的存储、管理、检索、浏览和提取都非常困难。另一方面，从航空物探专业应用需求来说，很少对单个测点的场值数据进行运算、分析等 *** 作，一般至少是对一条测线或以上测线，多数时候是需要对整个测区的场值数据进行化极、上延、正反演拟合等。

因此，在航空物探数据库表结构设计时，改变过去将基准点或场值点数据记录作为数据库最小管理对象的理念，采用了大字段存储技术，将测线作为数据库最小管理对象，将测线上的测量数据，如坐标数据和磁场、重力场数据分别存储在相应大字段中。在航空物探数据库建设中，大量采用数据库的大字段存储技术（详见《航空物探信息系统数据库结构设计》）。

（三）大字段存储效率

以航磁测量数据为例分析大字段存储技术优势。如果以场值存储方式存储测线数据，则每条记录包含架次号、测线号、基准号、地理坐标、投影坐标、磁场数据等，由于坐标数据采样率2次/s，磁场数据采样率10次/s，每5个磁场数据中，只有第1个磁场数据有坐标数据，其他4个坐标数据是内插出来，因此在测线记录中会产生大量冗余的数据坐标数据。采用点存储方式存储的测线数据记录数等于线上基准点数，若采用大字段存储方式，一条测线数据只存储为1条数据记录（图2-7），一般一条测线的测点数近万个，甚至更多，可见采用大字段存储大大减少测线数据存储记录数，提高数据的存取效率。

以某测区的两条航迹线为例，分别采用3种方式测试数据库的数据存储效率。磁场数据的采样率10次/s，坐标数据采样率2次/s，两条测线上共有基准点8801个。以场值方式存储先内插坐标信息，使得每个场值数据都拥有自己的坐标，然后存入数据库，共有数据记录44005条，写入数据库时间为5722 s，读取时间为103 s。第二种方式是以采样点的方式进行存储，共有8801条记录，写入数据库时间为947 s，读取需要091 s。第三种方式是以大字段的形式存储，只有2条记录，写入数据库103 s，读取时间为044 s（表2-2）。大字段数据存储记录数最少，存取效率最高。用整个测区数据测试效果更加明显。

表2-2 三种数据存储方法的存取效率比较

图2-7 大字段存储方式示意图

二、联合主键

主外键是关系型数据库建立表间关系的核心。在航空物探空间数据库建设过程中，要素类与要素类之间、要素类与对象类之间，以及对象类与对象类之间的关系的描述有3种形式，即拓扑关系——描述要素类与要素类之间结点、邻接和联通关系；叠加关系——描述要素类与要素类之间的相交、包含与分类关系；隶属关系——描述对象类与对象类之间的派生关系。前两种关系是采用空间数据模型建立的关系，而隶属关系是通过主键建立的对象类与对象类之间的关系。在建立一对一、一对多的表间关系时，需要在整个数据库表中确定具有唯一性的一个字段作为主键（主关键字）。

按照传统的航空物探数据的档案管理模式，每个项目分配一个自然数作为档案号，项目的所有资料均与此档案号相联系。勘查项目和科研项目的档案号是独立编号的，且均从001开始。加之人工管理的原因，存在1个项目2个档案号和2个项目1个档案号的情况，因此现行的档案号与项目之间的对应关系不具备唯一性，不能作为项目的唯一标识，即不能作为数据库表的主键。项目编号也不能作为数据库表的主键，项目编号也只是近十年的事，以前的项目没有项目编号。

综合考虑上述因素和项目具有分级、分类的特点，提出了构造项目唯一标识码（简称“项目标识”）的方法，并以此码作为数据库表的主键。

项目标识（主键）：AGS+项目类别（2位）+项目起始年份（4位）+档案号（6位）

标识含义：AGS——航空物探的缩位代码；

项目类别——2位代码，01代表勘查项目、02代表科研项目；

起始年份——4位代码，项目开始年号；

档案号——6位代码，为了与传统的项目管理方式相衔接，后面3～4位是

项目档案管理模式下的档案号，不足部分补零。

以上15位编码是一级项目的项目标识，二级及其以下级别的项目标识是在上一级项目标识基础上扩展2位数字代码，中间用“”号隔开，数字为该级项目的序号。项目标识定义为30位编码，适用于六级以内的项目。例如：AGS022004000576080402，表示该项目为2004年开展的档案号为576的航空物探科研项目（一级项目）的第8课题（二级项目）第4子课题（三级项目）的第2专题。由此可见，该项目标识不仅仅是一个建立表间关系的关键字，同时还表达了不同级别项目间的隶属关系。在系统软件开发时，利用此关系生成了项目的分级树形目录，用户对项目的层次关系一目了然，便于项目查询。

数据库的主键一经确定，相应地需要确定联合主键的组成及其表达方式。所谓联合主键就是数据资料的唯一标识，在一个数据库表中选择2个或者2个以上的字段作为主键。由于航空物探数据绝大部分与项目标识有关，加之数据的种类较多，分类复杂，单凭主键确定数据库表中记录的唯一性，势必需要构建极其复杂的主键，这种方法既不利于主键的数据 *** 作，又会造成大量的数据冗余，合理地使用联合主键技术可以很好地解决资料唯一问题。以项目提交资料为例，提交的资料分为文字类资料、图件类资料和媒体类资料，我们对资料进行分类和编号，例如100代表文字资料（110——World文档，120——PDF文档），200代表图件资料（210——基础地理资料、220——基础地质资料，230——航迹线图，240——剖面图，250——等值线图等），300代表媒体资料（310——PPT文档，320——照片等），第1位（百位）表示该资料的类型，第2～3位表示该类资料的序号。

在数据库管理和项目资料查询时，采用项目标识与资料分类编号作为联合主键（图2-8），可以高效地实现复杂数据的查询。在整个数据库系统中多处（项目查询、数据提取等模块）使用联合主键技术。

图2-8 联合主键实例

三、信息标准化

为了实现数据共享，在航空物探数据库建模过程中，参考和引用了近百个国家信息化标准，编制了4个中心信息化标准和1个图件信息化工作指南。

（一）引用的国家信息化标准

1）地质矿产术语分类代码：地球物理勘查，地球化学勘查，大地构造学，工程地质学，结晶学及矿物学，矿床学，水文地质学，岩石学，地质学等。

2）国家基础信息数据分类与代码，国土基础信息数据分类与代码，地球物理勘查技术符号，地面重力测量规范，地面磁勘查技术规程，地面高精度磁测技术规程，大比例尺重力勘查规范，地理信息技术基本术语，地理点位置的纬度、经度和高程的标准表示法，地名分类与类别代码编制规则。

3）地球空间数据交换格式；数学数字地理底图数据交换格式；数字化地质图图层及属性文件格式。

（二）本系统建立的信息化标准

编写了“航空物探空间数据要素类和对象类划分标准”，“航空物探项目管理和资料管理分类代码标准”，“航空物探勘查分类代码标准”，“航空物探信息系统元数据标准”，“航空物探图件信息化工作指南”，以便与其他应用系统进行信息交换，实现数据库资料共享。

航空物探空间数据要素类和对象类划分标准：根据物探方法、数据处理过程以及推断解释方法和过程，把与GIS有关的数据划分为不同类型的要素类-对象类数据，按专业、比例尺、数据内容对要素类和对象类进行统一命名，使空间数据库中的每个要素类和对象类的命名具有唯一性，防止重名出现。规定要素类-对象类数据库表结构及数据项数值类型。

航空物探项目管理和资料管理分类代码标准：规定了航空物探项目管理和资料管理的相关内容，包括航空物探勘查项目和科研项目的项目立项、设计、实施、成果、评审、资料汇交等项目管理的全过程中的内容，以及项目成果资料和收集资料的归档、发送、销毁、借阅等资料管理与服务过程中的内容和数据项代码。

航空物探勘查分类代码标准：在“地质矿产术语分类代码 地球物理勘查”（国家标准GB/T 964928—1998）增加了航磁、航重专业方面所涉及的数据采集、物性参数、方法手段、仪器设备、资料数据解释及成图图件等内容和数据项代码。

航空物探信息系统元数据标准：规定了航空物探空间数据管理与服务的元数据（数据的标识、内容、质量、状况及其他有关特征）的内容。

四、航迹线数据模型

（一）航迹线模型的结构

航空物探测量是依据测量比例尺在测区内布置测网（测线和切割线）。当飞机沿着设计的测线飞行测量时，航空物探数据收录系统按照一定的采样率采集采样点的地理位置、高度和各种地球物理场信息。采用属性数据分置的方法，将测线地理位置信息从航空物探测量数据中分离出来，形成航迹线要素类表，在此表中只存储与航迹线要素类有关的数据，如项目标识、测区编号、测线号、测线类型（用于区分测线、切割线、不同高度线、重复线等）、坐标、高度值等；将航迹线的对象类数据（磁场、重力场基础数据）分别以大字段形式存储在各自的二维表中，它们共享航迹线，解决了多源有序不同采样率的航空物探测量数据的数据存储问题，在满足要素类空间查询的同时，统一数据的存储方式（图2-9）。航迹线要素类隶属于测区要素类，它们之间为空间拓扑（包含）关系。测区从属于勘查项目，每个勘查项目至少有一个测区，它们之间为1对多关系。有关项目信息存放在项目概况信息对象类表中，各种表之间通过项目标识进行联接。

图2-9 航迹线数据模型结构

（二）航迹线的UML模型

统一建模语言UML（Unified Modeling Language）是一种定义良好、易于表达、功能强大且普遍适用的建模语言。它溶入了软件工程领域的新思想、新方法和新技术。UML是面向对象技术领域内占主导地位的标准建模语言，成为可视化建模语言的工业标准。在UML基础上，ESRI定义了空间数据库建模的ArcGIS包、类库和扩展原则。

图2-10 与航迹线有关的数据库表逻辑模型结构图

在确定航迹线数据模型后，以它为基础，使用UML完成与航迹的有关的项目概况信息、测区信息、原始数据等数据库表逻辑模型设计（图2-10）。

由UML模型生成Geodatabase模式时，模型中的每个类都对应生成一个要素类或对象类。类的属性映射为要素类或对象类的字段。基类属性中包含的字段，在继承类中不需重复创建。例如，每个类都包括项目标识等字段，可以创建一个包含公共属性的基类，其他类从该类继承公共的属性，而无需重复建基类中包含的属性。因为基类没有对应的要素类或对象类，所以将基类设置为抽象类型。要素类之间的关系采用依赖关系表示。

五、数据库逻辑模型

关系数据库的逻辑结构由一组关系模式组成，因而从概念结构到关系数据库逻辑结构的转换就是将概念设计中所得到的概念结构（ER图）转换成等价的UML关系模式（图2-11）。在UML模型图中，要素数据集用Geodatabase工作空间下的静态包表示。要素集包不能互相嵌套，为了容易组织，在生成物理模型后，在要素数据集包中自定义嵌套。要素数据集与空间参考有关，但是空间参考不能在UML中表达。要素类和二维表都是以类的形式创建的，区别是要素类继承Feature Class的属性，而二维表继承Object属性。为了表达每种元素的额外属性，比如设置字符型属性字段的字符串长度，设置要素类的几何类型（点、线或面）需要使用Geodatabase预定义的元素标记值。

图2-11 逻辑设计关系转换

基于航空物探数据的内在逻辑关系进行分析，使用统一建模语言（UML）构建数据实体对象间的关系类，定义了航空物探数据库的逻辑模型（图2-12）。

最近在进行UML学习过程中，突然忘记了大学时关于数据库理论中概念模型、逻辑模型、物理模型之间的区别。随机复习上网并复习，并在此记录一下，数据库建模是对现实世界进行分析、抽象、并从中找出内在联系，进而确定数据库的结构。

1、概念模型：就是从现实世界到信息世界的第一层抽象，确定领域实体属性关系等，使用E-R图表示，E-R图主要是由实体、属性和联系三个要素构成的。

2、逻辑模型：是将概念模型转化为具体的数据模型的过程，即按照概念结构设计阶段建立的基本E-R图，按选定的管理系统软件支持的数据模型（层次、网状、

关系、面向对象），转换成相应的逻辑模型。这种转换要符合关系数据模型的原则。目前最流行就是关系模型（也就是对应的关系数据库）

E-R图向关系模型的转换是要解决如何将实体和实体间的联系转换为关系，并确定这些关系的属性和码。这种转换一般按下面的原则进行：

（1）一个实体转换为一个关系，实体的属性就是关系的属性，实体的码就是关系的码。

（2）一个联系也转换为一个关系，联系的属性及联系所连接的实体的码都转换为关系的属性，但是关系的码会根据联系的类型变化，如果是：

1：1联系，两端实体的码都成为关系的候选码。

1：n联系，n端实体的码成为关系的码。

m：n联系，两端实体码的组合成为关系的码。

3、物理模型就是根据逻辑模型对应到具体的数据模型的机器实现。物理模型是对真实数据库的描述。如关系数据库中的一些对象为表、视图、字段、数据类型、长度、主键、外键、索引、约束、是否可为空、默认值。

层次模型、网状模型、关系模型

层次模型（格式化模型）

定义和限制条件：有且仅有一个节点，无父节点，此节点为树的根；其他节点有且仅有一个父节点；

优点：

①数据结构简单清晰；

②利用指针记录边向联系，查询效率高；

③良好的完整新支持；

缺点：

①只能表示1：N的联系。尽管有许多辅助手段实现M：N的联系，但比较复杂，不易掌握。

②层次模型的树是有序树（层次顺序）。对任一结点的所有子树都规定了先后次序，这一限制隐含了对数据库存取路径的控制。

③树中父子结点之间只存在一种联系，因此，对树中的任一结点，只有一条自根结点到达它的路径。

网状模型（格式化模型）

网状模型的2个特征：允许一个以上的节点无双亲；一个节点可以有多于一个的双亲；

优点：

①可以更加清晰表达现实，符合现实中的数据关系；

②可以很快存取 *** 作；

缺点：

①结构复杂；

②不易掌握，网状模型的DDL，DDM复杂，并且并且要嵌入某一种高级语言（COBOL，c），用户不易掌握；

③应用程序复杂，记录之间的联系通过存取路径实现的，应用程序在访问数据时必须选择合适的存取路径，因此用户必须了解系统结构的细节，加重编写应用程序的负担；

关系模型

单一的数据结构——关系

现实世界的实体以及实体间的各种联系均用关系来表示，从用户角度看，关系模型中数据的逻辑结构是一张二维表。

优点：

①数据结构单一，关系模型中，不管是实体还是实体之间的联系，都用关系来表示，而关系都对应一张二维数据表，数据结构简单、清晰。

②关系规范化，并建立在严格的理论基础上，构成关系的基本规范要求关系中每个属性不可再分割，同时关系建立在具有坚实的理论基础的严格数学概念基础上。

③概念简单， *** 作方便，关系模型最大的优点就是简单，用户容易理解和掌握，一个关系就是一张二维表格，用户只需用简单的查询语言就能对数据库进行 *** 作。

缺点：

①查询效率不如格式化数据模型；

②为了提高性能，数据库管理系统需要优化用户查询，增加了数据库管理系统的开发难度；

关系数据库模型支持了SQL语言的发展，并且拥有强大的理论基础为后盾(基于一阶的谓词逻辑)，目前，SQL已经成为定义和 *** 纵关系数据库的标准语言。

关系数据模型的另一个好处在于它的简单性、适合联机事务处理(OLTP)、支持数据独立性。但是关系数据模型特别是RDBMS同样存在许多的不足之处。详细内容请参考下文：

一对“现实世界”实体的表达能力比较弱

规范化通常导致表与“现实世界”中的实体不对应，它将“现实世界”中的实体分割成几张表来显示，以物理表示法来反映实体结构，这样效率会比较差，常常要在查询处理中进行很多连接 *** 作。

二语义过载

关系模型表达数据和数据间关系的构造只有一种——表。例如，为了表达实体A和实体B之间的多对多(:)关系、我们需要创建三张表，两个分别用于表达实体A和B，第三张表用于表达实体间的关系。它没有一种机制来区分实体和关系，也无法区分在实体间存在的不同种类的关系。例如，一个1:关系可能是Has、Supervises、Manages等等。如果可以进行区分，也许我们就可以将语义构建到 *** 作中。所以，我们说关系模型语义过载了。

三不能很好的支持业务规则

很多商业化系统不能完全支持实体和参照完整性、域等业务规则，所以需要将它们内置到应用程序中。这样当然是危险的，而且容易导致做重复的工作。更糟糕的是，可能还会引起不一致现象。而且，在关系模型中不支持其他类型的业务规则，这又意味着它们需要被构建到DBMS或应用程序中。

四有限的 *** 作

关系模型只有一些固定的 *** 作集，例如面向集合和记录的 *** 作， *** 作是在SQL规格说明中提供的。但是，SQL目前不允许指定新的 *** 作。因此，在给许多“现实世界”对象的行为建模就有了太多的限制。例如，一个GIS应用程序典型的使用点、线、线组、多边形和一些处理距离、交叉点和包含关系的 *** 作。

五处理递归查询困难

数据的原子性意味着在关系模型中不允许出现重复的数据组，这样就导致了处理递归查询极为困难。递归查询就是那些有关表和自身直接或间接的关系的查询。为了解决这个问题，SQL可以嵌入在一个高级程序设计语言中，由高级程序设计语言来提供支持反复 *** 作的功能。而且，很多RDBMS提供了具有类似结构的报表书写程序。不管是哪种情况，都是应用程序而不是系统的内在功能提供了所需的功能。

六阻抗失配

直到最新版本的SQL标准，都缺少完全的计算功能。为了解决这个问题并且提供更多的灵活性，SQL标准提供嵌入式SQL来帮助开发更加复杂的数据库应用程序。但是，这引起了阻抗不匹配(impedance mismatch)的问题，因为我们将两种不同的程序设计模式混合在了一起。

1SQL是一种处理行数据的声明性语言，而诸如C语言这样的高级语言则是过程化的语言，一次只能处理一行数据。

2SQL和3GL使用不同的模型来表达数据。比如，SQL提供内置的数据类型Date(日期型)和Interval(时间间隔型)，而在传统的编程语言中却没有这样的类型。因此，就需要应用程序在两种表示法之间进行转换。而这样做无论从程序设计的工作量还是运行时资源的使用来看都是低效的。而且，由于我们使用两种不同的系统，因此，不可能将类型检测作为一个整体自动进行。

注：SQL标准(SQL3)通过引入许多新的特征已经弥补了上文中讲述的一些不足之处。

根据数据结构划分,数据库模型分为:关系,层次,网状模型

关系模型用表的 来表示数据和数据间的联系每个表有多个列,每列有唯一的列名在关系模型中,无论是从客观事物中抽象出的实体,还是实体之间的联系,都用单一的结构类型——关系来表示在对关系进行各种处理之后,得到的还是关系——一张新的二维表

关系模型比层次和网状模型更能保证数据的一致性和完整性,但是在查询效率上不如其他两种模型

希望回答对你有所帮助

本笔记将利用sql语言构建RFM模型，将会有两种办法对用户进行分类。

第一种方法是基于有明确业务指标计算RFM分值。

第二种是按二八定律设定阀值。

首先看看RFM模型是什么？

R值:Rencency(最近一次消费) 指的是用户在店铺最近一次购买时间距离分析点的时间间隔;

F值:Frequency(消费频率) 指的是是用户在固定时间内的购买次数;

M值:Monetary(消费金额) 指的是一段时间(通常是1年)内的消费金额;

根据三个值的高低之分，会得出8种类型的客户；

一般每个指标都会有1，2，3，4，5分的分值标准，此指标一般根据具体业务需求进行设置；

如：

然后根据以上标准对用户进行打分，并会求得各指标均值，进行比较，大于均值为高，少于均值为低。

接下来按照此标准用sql执行。

首先我们导入相关数据，并去重数据放进新表 temp_trade；

由于时间关系，以导入如下数据，期间利用

SET date_time = STR_TO_DATE(time,'%Y-%m-%d %H');

set dates=date(date_time);

这两个函数对原表（红框）日期进行处理；

再检查一下关键字段有无缺失值

查询后得出并无缺失。

再检查一下用户行为是否有1、2、3、4以外的异常值；

查询结果无异常值；

-- 建新表，放进 去重后的 数据

create table temp_trade like o_retailers_trade_user;

insert into temp_trade select distinct from o_retailers_trade_user;

SELECT user_id , max(dates) AS 最近一次消费时间

FROM

temp_trade

WHERE behavior_type='2'

GROUP BY user_id

ORDER BY 最近一次消费时间 desc

CREATE VIEW r_clevel AS

SELECT user_id , 最近一次消费时间 , DATEDIFF('2019-12-19',最近一次消费时间) AS 相差天数,

(CASE

WHEN DATEDIFF('2019-12-19',最近一次消费时间)<=2 THEN 5

WHEN DATEDIFF('2019-12-19',最近一次消费时间)<=4 THEN 4

WHEN DATEDIFF('2019-12-19',最近一次消费时间)<=6 THEN 3

WHEN DATEDIFF('2019-12-19',最近一次消费时间)<=8 THEN 2

ELSE

1 END )AS R分值

FROM

(

SELECT user_id , max(dates) AS 最近一次消费时间

FROM

temp_trade

WHERE behavior_type='2'

GROUP BY user_id

ORDER BY 最近一次消费时间 desc

)a

SELECT user_id , COUNT(user_id) AS 购买频次

FROM

temp_trade

WHERE behavior_type='2'

GROUP BY user_id

ORDER BY 购买频次 desc

CREATE VIEW f_clevel AS

SELECT user_id , 购买频次 ,

(CASE

WHEN 购买频次<=2 THEN 1

WHEN 购买频次<=4 THEN 2

WHEN 购买频次<=6 THEN 3

WHEN 购买频次<=8 THEN 4

ELSE 5 END )AS F分值

FROM

(

SELECT user_id , COUNT(user_id) AS 购买频次

FROM

temp_trade

WHERE behavior_type='2'

GROUP BY user_id

)a

-- 1R平均值

SELECT avg(R分值) as 'r_avg' FROM r_clevel;

-- 2F平均值

select avg(F分值) as 'f_avg' from f_clevel;

create view RFM_table

as

select a,b分值,

(case

when a分值>25515 and b分值>22606 then '重要高价值客户' when a分值<25515 and b分值>22606 then '重要唤回客户'

when a分值>25515 and b分值<22606 then '重要深耕客户' when a分值<25515 and b分值<22606 then '重要挽留客户' END

) as user_class

from r_clevel a, f_clevel b

where auser_id=buser_id;

SELECT user_class , COUNT(user_class)AS 数量

FROM

RFM_table

GROUP BY user_class

SELECT COUNT(DISTINCT user_id) AS 购买用户数

FROM

temp_trade

WHERE behavior_type='2'

SELECT

相差天数

FROM

(

SELECT user_id , 最近一次消费时间 , DATEDIFF('2019-12-19',最近一次消费时间) AS 相差天数

FROM

(

SELECT user_id , max(dates) AS 最近一次消费时间

FROM

temp_trade

WHERE behavior_type='2'

GROUP BY user_id

ORDER BY 最近一次消费时间 desc

)a

ORDER BY 相差天数 DESC

)b

LIMIT 32,1

SELECT

购买频次

FROM

(

SELECT user_id , 购买频次

FROM

(

SELECT user_id , COUNT(user_id) AS 购买频次

FROM

temp_trade

WHERE behavior_type='2'

GROUP BY user_id

ORDER BY 购买频次 DESC

)a

)b

LIMIT 32,1

CREATE VIEW RF_TABLE AS

SELECT user_id , 最近一次消费时间 , DATEDIFF('2019-12-19',最近一次消费时间) AS 相差天数,购买频次

FROM

(

SELECT user_id , max(dates) AS 最近一次消费时间 , COUNT(user_id) AS 购买频次

FROM

temp_trade

WHERE behavior_type='2'

GROUP BY user_id)a

select user_id,

(case

when 相差天数<=19 and 购买频次>=7 then '重要高价值客户' when 相差天数>19 and 购买频次>=7 then '重要唤回客户'

when 相差天数<=19 and 购买频次<7 then '重要深耕客户' when 相差天数>19 and 购买频次<7 then '重要挽留客户' END

) as user_class

from RF_TABLE ;

SELECT user_class , COUNT(user_class)AS 数量

FROM

(

select user_id,

(case

when 相差天数<=19 and 购买频次>=7 then '重要高价值客户' when 相差天数>19 and 购买频次>=7 then '重要唤回客户'

when 相差天数<=19 and 购买频次<7 then '重要深耕客户' when 相差天数>19 and 购买频次<7 then '重要挽留客户' END

) as user_class

from RF_TABLE

) a

GROUP BY user_class

以上就是关于数据库逻辑模型全部的内容，包括:数据库逻辑模型、SQL建表概念模型和物理模型的例子(sql数据模型)、数据库建模的介绍等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！