热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
登录
注册
【与达梦同行】达梦数据库理论认识及实战使用
技术分享/
文章详情
/
【与达梦同行】达梦数据库理论认识及实战使用
亨利
2022/12/07
3699
6
6
前文
因为工作的性质，经常会接触到各种各样的数据库，因为达梦是老三厂之首【达梦、金仓、南大】，所以方方面面会有一些了解 。本文从多种角度出发， 达梦是谁能做什么以及产品安装使用来介绍达梦，最后引进案例多个Oracle脚本操作实践在DM8平台，让各位对达梦轮廓有一个立体的认识。
达梦是谁
从市场规模、现有产品、本身定位、市场宣传、路径发展、技术特征、技术布道的角度来介绍达梦是谁
市场规模
-
老牌数据库中的市场份额空间最大
-
国内金融、电力、能源、电信、民航、电子政务领域均有案例
-
海外市场业务也有一定案例，包括泰国教育云平台以及印尼、埃塞俄比亚、保加利亚、尼日利亚、津巴布韦、韩国、俄罗斯
现有产品
达梦公司以自主先进的达梦数据库(MPP、DSC、RWC、DATAWATCH)为核心，旨在为政府、企事业单位提供云计算及大数据平台服务，平台能对接、管理异构公有云和私有云，提供达梦全栈数据产品&解决方案，实现应用快速部署与自动化运维管理，集成达梦特有的DMETL、DMHS、数据分析与可视化、数据挖掘等工具，融合开源大数据产品，打造适应不同应用场景的大数据产品体系，为用户提供差异化服务。
-
达梦数据库管理系统DM8
-
达梦数据共享集群DMDSC
-
达梦分析型大规模数据处理集群DMMP
-
达梦数据守护集群DMDataWatch
-
达梦读写分离集群DMRWC
-
新一代分布式数据库DMDPC
-
达梦数据交换平台软件DMETL
-
云数据库
-
达梦数据实时同步DMHS
-
图数据库系统产品
-
达梦新云数据库（for redis) DMNCDB
本身定位
达梦的最大市场对手对标Oracle，并且会吸收多样化架构充分满足不同场景需求，支持超大规模OLTP和OLAP处理技术， 致力于数据的全栈解决方案
市场宣传
打造完全自主知识产权的优质国产数据库, 为客户提供 IT 信息化整体解决方案
路径发展
借鉴当前先进新技术思想与主流数据库产品的优点，融合了分布式、弹性计算与云计算的优势
技术特征
为了吞食Oracle的市场，达梦的内部和外部模仿Oracle，从操作使用、语义语法、存储过程、函数视图到迁移、升级，达梦都会与Oracle兼容
技术布道
达梦是100%的闭源商品，外面工程师受众少，但是相对南大通用和金山，达梦与工程师联动较多，除了征文、比赛，达梦还在社区、文档方面下了功夫。
达梦能干什么
达梦是旗下有多款不同数据库产品覆盖所有的业务应用场景，能力值具备广度，兼顾深度。Oracle能做的，达梦都能做，Oracle有的产品生产线有的东西，达梦都有。达梦数据库代表产品是dm8，这个对标Oracle官方的oracle12c以上的。dm8是一个能跨越多种软硬件平台，具有大型数据综合管理能力且高效稳定的通用数据库管理系统，而且与Oracle、SQL Server等主流数据库具有高度的兼容性。dm8在支持应用系统开发及数据处理方面的主要特点体现如下。
1.支持案例高效的服务端存储模块的开发
2.具有符合国际通用标准或行业标准的数据库访问和操作接口。
3.高度兼容Oracle、SQL Server等主流数据库管理系统
4.支持国际化应用开发
5.自适应各种软硬件平台
达梦数据库安装
达梦DM8支持容器和软件版安装，下面介绍DM8安装
下载DM8镜像
wget https://download.dameng.com/eco/dm8/dm8_20220822_rev166351_x86_rh6_64_ctm.tar
导入DM8镜像
[root@111 ~]# docker load -i dm8_20220822_rev166351_x86_rh6_64_ctm.tar
[root@111 ~]# docker images | grep dm8
dm8_single v8.1.2.128_ent_x86_64_ctm_pack4 ccb727ce9dce 3 months ago 432MB
运行容器
docker run -d -p 5236:5236 --restart=always --name dm8_01 --privileged=true -e PAGE_SIZE=16 -e LD_LIBRARY_PATH=/opt/dmdbms/bin -e INSTANCE_NAME=dm8_01 -v /se2/docker/dm8/data:/opt/dmdbms/data dm8_single:v8.1.2.128_ent_x86_64_ctm_pack4
检查数据库日志
看到DM Database is OK，就知道数据库启动成功
[root@hybriddb03 ~]# **docker logs -f dm8_01**
file dm.key not found, use default license!
License will expire on 2023-08-04
Normal of FAST
Normal of DEFAULT
Normal of RECYCLE
Normal of KEEP
Normal of ROLL
log file path: /opt/dmdbms/data/DAMENG/DAMENG01.log
log file path: /opt/dmdbms/data/DAMENG/DAMENG02.log
write to dir [/opt/dmdbms/data/DAMENG].
create dm database success. 2022-12-08 15:48:33
initdb V8
db version: 0x7000c
Init DM success!
Start DmAPService...
Starting DmAPService: [ OK ]
/opt/dmdbms/conf/dm.ini does not exist, use default dm.ini
Start DMSERVER success!
Dmserver is running.
DM Database is not OK, please wait...
DM Database is not OK, please wait...
DM Database is not OK, please wait...
DM Database is not OK, please wait...
DM Database is not OK, please wait...
DM Database is OK
Finished soft link DM current dm_DMSERVER_202212.log to dm_DMSERVER.log
* Starting periodic command scheduler cron
达梦数据库操作使用
命令行操作
登录进入DM8的容器
docker exec -it dm8_01 bash
进入 /opt/dmdbms/bin目录
root@20f7fc6a2f51:/opt/dmdbms/bin# cd /opt/dmdbms/bin
运行disql附带SYSDBA和默认密码SYSDBA001， 就可以进入DM8的命令行控制管理台
root@20f7fc6a2f51:/opt/dmdbms/bin# ./disql SYSDBA/SYSDBA001
Server[LOCALHOST:5236]:mode is normal, state is open
login used time : 2.228(ms)
disql V8
SQL>
dbeaver操作
除了通过命令行界面操作数据，也可以通过图形化界面操作数据库，开源主流dbeaver默认不支持dm8，可以通过自动加载的方式支持
驱动包下载
官方文档搜索 应用开发指南 查找，选择JAVA语言，再点击MyBatis框架，最后点击项目所需求的jar包，这里有我们需要的jdbc驱动包。 如下图所示，https://eco.dameng.com/document/dm/zh-cn/app-dev/java-MyBatis-frame.html
打开DBeaver,点击数据库，再点驱动管理器，如下图
驱动名称: dm8
类名: dm.jdbc.driver.DmDriver
URL模板: jdbc:dm://{host}:5236
默认端口: 5236
点击库，引进DmJdbcDriver18.jar
新建连接，选择dm8，使用创建的用户名和密码访问dm8
成功登录后可以看到如下信息。
DM8日常使用
1，查看数据库实例
SQL> select * from v$instance;
LINEID NAME INSTANCE_NAME INSTANCE_NUMBER HOST_NAME SVR_VERSION DB_VERSION START_TIME STATUS$ MODE$ OGUID DSC_SEQNO DSC_ROLE BUILD_VERSION BUILD_TIME
---------- ---- ------------- --------------- --------- ----------- ---------- ------------------- ------- ----- ----------- ----------- -------- ------------- ----------
1 DMSERVER DMSERVER 1 20f7fc6a2f51 DM Database Server x64 V8 DB Version: 0x7000c 2022-12-08 15:48:48 OPEN NORMAL 0 0 NULL 1-2-128-22.08.04-166351-20005-CTM Aug 4 2022 12:02:20
used time: 0.263(ms). Execute id is 56305.
2，查看表空间
SQL> select * from v$tablespace;
LINEID ID NAME CACHE TYPE$ STATUS$ MAX_SIZE TOTAL_SIZE FILE_NUM ENCRYPT_NAME ENCRYPTED_KEY COPY_NUM SIZE_MODE OPT_NODE USED_SIZE
---------- ----------- ---- ----- ----------- ----------- -------------------- -------------------- ----------- ------------ ------------- ----------- --------- ----------- --------------------
1 0 SYSTEM 1 0 0 3200 1 NULL NULL NULL NULL NULL 2112
2 1 ROLL 1 0 0 8192 1 NULL NULL NULL NULL NULL 1488
3 3 TEMP 2 0 0 1664 1 NULL NULL NULL NULL NULL 16
4 4 MAIN 1 0 0 8192 1 NULL NULL NULL NULL NULL 16
used time: 0.310(ms). Execute id is 56306.
3，查询服务器信息
SQL> select * from V$SYSTEMINFO;
LINEID N_CPU TOTAL_PHY_SIZE FREE_PHY_SIZE TOTAL_VIR_SIZE FREE_VIR_SIZE TOTAL_DISK_SIZE FREE_DISK_SIZE DRIVER_NAME DRIVER_TOTAL_SIZE DRIVER_FREE_SIZE LOAD_ONE_AVERAGE LOAD_FIVE_AVERAGE LOAD_FIFTEEN_AVERAGE CPU_USER_RATE CPU_SYSTEM_RATE CPU_IDLE_RATE SEND_BYTES_TOTAL RECEIVE_BYTES_TOTAL SEND_BYTES_PER_SECOND RECEIVE_BYTES_PER_SECOND SEND_PACKAGES_PER_SECOND RECEIVE_PACKAGES_PER_SECOND USED_PHY_SIZE
---------- ----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ----------- -------------------- -------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- -------------------- -------------------- --------------------- ------------------------ ------------------------ --------------------------- --------------------
1 72 540499267584 292804603904 0 0 4422879887360 4102784020480 NULL NULL NULL 2.700000107288361E-01 2.399999946355820E-01 4.600000083446503E-01 1.109395861625671E+00 1.551337987184525E-01 9.872386932373047E+01 0 656 0 0 0 0 40888090624
used time: 2.716(ms). Execute id is 56307.
4，查询会话连接信息
SQL> SQL> select * from v$sessions;
LINEID SESS_ID SESS_SEQ SQL_TEXT STATE N_STMT N_USED_STMT SEQ_NO CURR_SCH USER_NAME TRX_ID CREATE_TIME CLNT_TYPE TIME_ZONE CHK_CONS CHK_IDENT RDONLY INS_NULL COMPILE_FLAG AUTO_CMT DDL_AUTOCMT RS_FOR_QRY CHK_NET ISO_LEVEL CLNT_HOST APPNAME CLNT_IP OSNAME CONN_TYPE VPOOLADDR RUN_STATUS MSG_STATUS LAST_RECV_TIME LAST_SEND_TIME DCP_FLAG THRD_ID CONNECTED PORT_TYPE SRC_SITE MAL_ID CONCURRENT_FLAG CUR_LINENO CUR_MTDNAME CUR_SQLSTR CLNT_VER SQL_ID EID
---------- -------------------- ----------- -------- ----- ----------- ----------- ----------- -------- --------- -------------------- -------------------------- --------- --------- -------- --------- ------ -------- ------------ -------- ----------- ---------- ------- ----------- --------- ------- ------- ------ --------- -------------------- ---------- ---------- -------------------------- -------------------------- -------- ----------- ----------- ----------- ----------- -------------------- --------------- ----------- ----------- ---------- -------- ----------- --------------------
1 139903325246064 593 select * from v$sessions; ACTIVE 64 1 11 SYSDBA SYSDBA 3644 2022-12-08 15:51:42.000000 SQL3 +08:00 N N N Y N N Y N N 1 20f7fc6a2f51 disql ::ffff:127.0.0.1 Linux HOMOGENEOUS 139903325178392 RUNNING RECIEVE 2022-12-08 15:59:58.000000 2022-12-08 15:59:46.000000 N 188 1 0 65535 NULL 0 NULL NULL NULL 8.1.2.128 1210 -1
used time: 0.393(ms). Execute id is 56308.
5，查看数据库服务器配置参数
select * from v$dm_ini;
6，查询最近的 sql 执行记录
select * from v$sql_history;
7，查询所有的表
select TABLE_NAME from user_tables;
8，查询模式下所有表
select OWNER,TABLE_NAME from all_tables where owner='SYSDBA' limit 10;
select OWNER,TABLE_NAME from all_tables ;
select OWNER,TABLE_NAME from all_tables where owner='HENLEY' ;
9，查询SYSDBA用户下所有表字段
select * from all_tab_cols where owner='SYSDBA';
10，查看表注释
select * from ALL_TAB_COMMENTS;
11.创建表空间
SQL> create tablespace test1208 datafile 'user_test1208.dbf' size 128;
executed successfully
used time: 554.214(ms). Execute id is 56604.
12.创建用户并绑定表空间
SQL> create user henley identified by Gmcc123456 default tablespace test1208;
executed successfully
used time: 111.915(ms). Execute id is 56605.
13.用户授权
SQL> grant resource,public,soi,svi,vti to henley;
executed successfully
used time: 56.774(ms). Execute id is 56606.
注：如果不授权的话，默认新建的用户只有登陆权限，没有其他权限，如建表，查看信息等。
RESOURCE：创建对象等权限；
SOI：具有系统表的查询权限；
SVI：具有基础V视图的查询权限；
VTI：具有系统动态视图的查询权限，VTI默认授权给DBA且可转授;
PUBLIC：不可以创建数据库对象，只能对有权限的数据库对象进行数据操纵。
14. 创建一个schema名称
CREATE SCHEMA schema名称 AUTHORIZATION henley;
/
15. 模糊查询approx_count相似的函数
select a.NAME as "函数名称",b.NAME as "参数名称",DATA_TYPE as "参数类型" from v$ifun as a JOIN v$ifun_arg as b on a.id=b.id where a.name like '%approx_count%';
select a.NAME as "函数名称",b.NAME as "参数名称",DATA_TYPE as "参数类型" from v$ifun as a JOIN v$ifun_arg as b on a.id=b.id;
案例实践
DM8对标Oracle，把Oracle作为假为假想敌，SQL方面做得高度兼容，你可以像使用Oracle一样使用DM8。而Oracle在SQL做得有多深，行业标准SQL规范是Ansi SQL，但是Oracle扎根业务深知用户，它已经超过行业标准，发展出自己独特的Oracle SQL规范，Oracle比起其它数据库产品有更多函数和新功能。本读者找了Oracle业务常用的30个脚本，里面含有数据库表的表创建、函数运行、各种关联函数据，传到DM8平台后，直接在DM8运行以下脚本。
start /opt/dmdbms/data/dm8sql/yes/analytics-Deleting-Duplicates.sql start /opt/dmdbms/data/dm8sql/yes/analytics-First-look-at-Window-functions.sql start /opt/dmdbms/data/dm8sql/yes/analytics-Grouping-ranges-of-data.sql start /opt/dmdbms/data/dm8sql/yes/analytics-How-null-values-are-treated.sql start /opt/dmdbms/data/dm8sql/yes/analytics-How-to-apply-predicates.sql start /opt/dmdbms/data/dm8sql/yes/analytics-Hypothetical-ranking-and-RATIO_TO_REPORT.sql start /opt/dmdbms/data/dm8sql/yes/analytics-In-list-processing.sql start /opt/dmdbms/data/dm8sql/yes/analytics-LAG-and-LEAD-extensions.sql start /opt/dmdbms/data/dm8sql/yes/analytics-LISTAGG-with-the-OVER-clause.sql start /opt/dmdbms/data/dm8sql/yes/analytics-More-complex-ranking-functions.sql start /opt/dmdbms/data/dm8sql/yes/analytics-More-ranking-options.sql start /opt/dmdbms/data/dm8sql/yes/analytics-More-window-clause-examples.sql start /opt/dmdbms/data/dm8sql/yes/analytics-Partition-clause-with-aggregation.sql start /opt/dmdbms/data/dm8sql/yes/analytics-Partitioned-Outer-Join.sql start /opt/dmdbms/data/dm8sql/yes/analytics-Ranking-part-3.sql start /opt/dmdbms/data/dm8sql/yes/analytics-Ranking-rows-across-an-entire-table.sql start /opt/dmdbms/data/dm8sql/yes/analytics-The-KEEP-clause.sql start /opt/dmdbms/data/dm8sql/yes/analytics-The-LISTAGG-function.sql start /opt/dmdbms/data/dm8sql/yes/analytics-The-RANGE-options-for-windows.sql start /opt/dmdbms/data/dm8sql/yes/analytics-Using-the-PARTITION-clause.sql start /opt/dmdbms/data/dm8sql/yes/analytics-Window-boundaries.sql start /opt/dmdbms/data/dm8sql/yes/analytics-Windows-and-null-values.sql start /opt/dmdbms/data/dm8sql/yes/analytics-Windows-and-the-NTH_VALUE-function.sql start /opt/dmdbms/data/dm8sql/yes/analytics-the-Tabitosan-method.sql
测试发现Oracle相关的数据类型、数据长度在DM8平台不需要任何改动，以下诸多函数listagg、date_taken-row_number、partition by contine、row_number() over、range between、last_value、nth_value、nvl(lag(loc) over、listagg(min(radius),‘,’)、listagg(min(radius),‘,’) within group ( order by planet )、ntile(4) over、decode(type,‘Ocean’,1,‘Sea’,2,3)、sum(sal) over () 、date_taken-row_number() over(order by date_taken)、dense_rank FIRST order by sal，在DM8平台都不需要改造，直接支持，有些SQL语句的控制逻辑在我这个常使用MySQL的人以为要改一下，结果发现不需要改。
也有一些不支持的函数例如approx_rank、approx_count，DM8就没有这些。
笔者把相关脚本上传了，喜欢的可以体验下。
附脚本的相关的执行过程
SQL> create table perth_weather (
SAMPLED DATE not null,
MIN_TEMP NUMBER(8,2) not null,
MAX_TEMP NUMBER(8,2) not null,
RAINFALL NUMBER(8,2) not null,
EVAP NUMBER(8,2) not null,
SUNSHINE NUMBER(8,2) not null,
WIND_DIR VARCHAR2(6) ,
MAX_WIND NUMBER(8,2) ,
MAX_WIND_TIME VARCHAR2(8) ,
TEMP_9AM NUMBER(8,2) not null,
HUMIDITY_9AM NUMBER(8,2) ,
CLOUD_9AM NUMBER(8,2) ,
WIND_DIR_9AM VARCHAR2(6) ,
WIND_SPEED_9AM NUMBER(8,2) ,
PRESSURE_9AM NUMBER(8,2) ,
TEMP_3PM NUMBER(8,2) not null,
HUMIDITY_3PM NUMBER(8,2) ,
CLOUD_3PM NUMBER(8,2) ,
WIND_DIR_3PM VARCHAR2(6) ,
WIND_SPEED_3PM NUMBER(8,2) ,
PRESSURE_3PM NUMBER(8,2)
);
executed successfully
SQL> select *
from water
order by 3 desc;
LINEID NAME TYPE SQUARE_KM
---------- ---- ---- -----------
1 Pacific Ocean Ocean 155557000
2 Atlantic Ocean Ocean 76762000
3 Indian Ocean Ocean 68556000
4 Southern Ocean Ocean 20327000
5 Arctic Ocean Ocean 14056000
6 Mediterranean Sea Sea 2965800
7 Caribbean Sea Sea 2718200
8 South China Sea Sea 2319000
9 Bering Sea Sea 2291900
10 Gulf of Mexico Gulf 1592800
11 Okhotsk Sea Sea 1589700
LINEID NAME TYPE SQUARE_KM
---------- ---- ---- -----------
12 East China Sea Sea 1249200
13 Hudson Bay Bay 1232300
14 Japan Sea Sea 1007800
15 Andaman Sea Sea 797700
16 North Sea Sea 575200
17 Red Sea Sea 438000
18 Baltic Sea Sea 422200
18 rows got
used time: 0.353(ms). Execute id is 61902.
SQL> select name, type, square_km,
sum(square_km) over ( order by square_km desc
rows between unbounded preceding and current row
) as tot
from water
order by 3 desc;
LINEID NAME TYPE SQUARE_KM TOT
---------- ---- ---- ----------- --------------------
1 Pacific Ocean Ocean 155557000 155557000
2 Atlantic Ocean Ocean 76762000 232319000
3 Indian Ocean Ocean 68556000 300875000
4 Southern Ocean Ocean 20327000 321202000
5 Arctic Ocean Ocean 14056000 335258000
6 Mediterranean Sea Sea 2965800 338223800
7 Caribbean Sea Sea 2718200 340942000
8 South China Sea Sea 2319000 343261000
9 Bering Sea Sea 2291900 345552900
10 Gulf of Mexico Gulf 1592800 347145700
11 Okhotsk Sea Sea 1589700 348735400
LINEID NAME TYPE SQUARE_KM TOT
---------- ---- ---- ----------- --------------------
12 East China Sea Sea 1249200 349984600
13 Hudson Bay Bay 1232300 351216900
14 Japan Sea Sea 1007800 352224700
15 Andaman Sea Sea 797700 353022400
16 North Sea Sea 575200 353597600
17 Red Sea Sea 438000 354035600
18 Baltic Sea Sea 422200 354457800
18 rows got
used time: 1.127(ms). Execute id is 61903.
SQL> select * from LAB_SAMPLES order by 2;
LINEID SAMPLE_ID DATE_TAKEN
---------- ----------- ----------
1 1 2015-12-01
2 2 2015-12-02
3 3 2015-12-03
4 4 2015-12-04
5 5 2015-12-07
6 6 2015-12-08
7 7 2015-12-09
8 8 2015-12-10
9 9 2015-12-14
10 10 2015-12-15
11 11 2015-12-16
LINEID SAMPLE_ID DATE_TAKEN
---------- ----------- ----------
12 12 2015-12-19
13 13 2015-12-20
13 rows got
used time: 0.261(ms). Execute id is 61921.
SQL> select
date_taken,
lag(date_taken) over ( order by date_taken) prev
from lab_samples
order by 1;
LINEID DATE_TAKEN PREV
---------- ---------- ----------
1 2015-12-01 NULL
2 2015-12-02 2015-12-01
3 2015-12-03 2015-12-02
4 2015-12-04 2015-12-03
5 2015-12-07 2015-12-04
6 2015-12-08 2015-12-07
7 2015-12-09 2015-12-08
8 2015-12-10 2015-12-09
9 2015-12-14 2015-12-10
10 2015-12-15 2015-12-14
11 2015-12-16 2015-12-15
LINEID DATE_TAKEN PREV
---------- ---------- ----------
12 2015-12-19 2015-12-16
13 2015-12-20 2015-12-19
13 rows got
used time: 2.122(ms). Execute id is 61922.
SQL> select
date_taken,
case
when nvl(lag(date_taken) over (order by date_taken),date_taken) != date_taken-1
then date_taken end loval
from lab_samples
order by 1;
LINEID DATE_TAKEN LOVAL
---------- ---------- ----------
1 2015-12-01 2015-12-01
2 2015-12-02 NULL
3 2015-12-03 NULL
4 2015-12-04 NULL
5 2015-12-07 2015-12-07
6 2015-12-08 NULL
7 2015-12-09 NULL
8 2015-12-10 NULL
9 2015-12-14 2015-12-14
10 2015-12-15 NULL
11 2015-12-16 NULL
LINEID DATE_TAKEN LOVAL
---------- ---------- ----------
12 2015-12-19 2015-12-19
13 2015-12-20 NULL
13 rows got
used time: 0.619(ms). Execute id is 61923.
SQL> select date_taken, max(loval) over (order by date_taken) loval
from (
select date_taken,
case
when nvl(lag(date_taken) over (order by date_taken),date_taken) !=
date_taken-1 then date_taken end loval
from lab_samples )
order by 1;
LINEID DATE_TAKEN LOVAL
---------- ---------- ----------
1 2015-12-01 2015-12-01
2 2015-12-02 2015-12-01
3 2015-12-03 2015-12-01
4 2015-12-04 2015-12-01
5 2015-12-07 2015-12-07
6 2015-12-08 2015-12-07
7 2015-12-09 2015-12-07
8 2015-12-10 2015-12-07
9 2015-12-14 2015-12-14
10 2015-12-15 2015-12-14
11 2015-12-16 2015-12-14
LINEID DATE_TAKEN LOVAL
---------- ---------- ----------
12 2015-12-19 2015-12-19
13 2015-12-20 2015-12-19
13 rows got
used time: 0.614(ms). Execute id is 61924.
SQL> select min(date_taken) range_start, max(date_taken) range_end
from (
select date_taken,max(loval) over (order by date_taken) loval
from (
select date_taken,
case
when nvl(lag(date_taken) over (order by date_taken),date_taken) !=
date_taken-1 then date_taken end loval
from lab_samples))
group by loval
order by 1;
LINEID RANGE_START RANGE_END
---------- ----------- ----------
1 2015-12-01 2015-12-04
2 2015-12-07 2015-12-10
3 2015-12-14 2015-12-16
4 2015-12-19 2015-12-20
used time: 0.182(ms). Execute id is 62121.
SQL> select p.name planet, m.name, m.radius,
row_number() over ( order by radius desc ) as size_rank
from planets p, moons m
where p.name = m.planet_name(+)
and p.name in ('Mars','Venus','Pluto','Mercury','Neptune')
order by size_rank;
LINEID PLANET NAME RADIUS SIZE_RANK
---------- ------ ---- ------- --------------------
1 Venus NULL NULL 1
2 Mercury NULL NULL 2
3 Neptune Triton 1353.40 3
4 Pluto Charon 603.60 4
5 Neptune Proteus 210.00 5
6 Neptune Nereid 170.00 6
7 Neptune Larissa 97.00 7
8 Neptune Galatea 88.00 8
9 Neptune Despina 75.00 9
10 Neptune Thalassa 41.00 10
11 Neptune Naiad 33.00 11
LINEID PLANET NAME RADIUS SIZE_RANK
---------- ------ ---- ------ --------------------
12 Neptune Halimede 31.00 12
13 Pluto Hydra 30.50 13
14 Neptune Neso 30.00 14
15 Pluto Nix 23.00 15
16 Neptune Sao 22.00 16
17 Neptune Laomedeia 21.00 17
18 Neptune Psamathe 20.00 18
19 Mars Phobos 11.10 19
20 Neptune S/2004 N 1 8.00 20
21 Pluto Kerberos 6.50 21
22 Mars Deimos 6.20 22
LINEID PLANET NAME RADIUS SIZE_RANK
---------- ------ ---- ------ --------------------
23 Pluto Styx 5.00 23
23 rows got
used time: 3.740(ms). Execute id is 62122.
SQL> select p.name planet, m.name, m.radius,
row_number() over ( order by radius desc NULLS LAST ) as size_rank
from planets p, moons m
where p.name = m.planet_name(+)
and p.name in ('Mars','Venus','Pluto','Mercury','Neptune');
LINEID PLANET NAME RADIUS SIZE_RANK
---------- ------ ---- ------- --------------------
1 Neptune Triton 1353.40 1
2 Pluto Charon 603.60 2
3 Neptune Proteus 210.00 3
4 Neptune Nereid 170.00 4
5 Neptune Larissa 97.00 5
6 Neptune Galatea 88.00 6
7 Neptune Despina 75.00 7
8 Neptune Thalassa 41.00 8
9 Neptune Naiad 33.00 9
10 Neptune Halimede 31.00 10
11 Pluto Hydra 30.50 11
LINEID PLANET NAME RADIUS SIZE_RANK
---------- ------ ---- ------ --------------------
12 Neptune Neso 30.00 12
13 Pluto Nix 23.00 13
14 Neptune Sao 22.00 14
15 Neptune Laomedeia 21.00 15
16 Neptune Psamathe 20.00 16
17 Mars Phobos 11.10 17
18 Neptune S/2004 N 1 8.00 18
19 Pluto Kerberos 6.50 19
20 Mars Deimos 6.20 20
21 Pluto Styx 5.00 21
22 Venus NULL NULL 22
LINEID PLANET NAME RADIUS SIZE_RANK
---------- ------ ---- ------ --------------------
23 Mercury NULL NULL 23
23 rows got
used time: 2.210(ms). Execute id is 62123.
SQL> select p.name planet, m.name, m.radius,
rank() over ( order by radius desc ) as size_rank
from planets p, moons m
where p.name = m.planet_name(+)
and p.name in ('Mars','Venus','Pluto','Mercury','Neptune')
order by size_rank;
LINEID PLANET NAME RADIUS SIZE_RANK
---------- ------ ---- ------- --------------------
1 Mercury NULL NULL 1
2 Venus NULL NULL 1
3 Neptune Triton 1353.40 3
4 Pluto Charon 603.60 4
5 Neptune Proteus 210.00 5
6 Neptune Nereid 170.00 6
7 Neptune Larissa 97.00 7
8 Neptune Galatea 88.00 8
9 Neptune Despina 75.00 9
10 Neptune Thalassa 41.00 10
11 Neptune Naiad 33.00 11
LINEID PLANET NAME RADIUS SIZE_RANK
---------- ------ ---- ------ --------------------
12 Neptune Halimede 31.00 12
13 Pluto Hydra 30.50 13
14 Neptune Neso 30.00 14
15 Pluto Nix 23.00 15
16 Neptune Sao 22.00 16
17 Neptune Laomedeia 21.00 17
18 Neptune Psamathe 20.00 18
19 Mars Phobos 11.10 19
20 Neptune S/2004 N 1 8.00 20
21 Pluto Kerberos 6.50 21
22 Mars Deimos 6.20 22
LINEID PLANET NAME RADIUS SIZE_RANK
---------- ------ ---- ------ --------------------
23 Pluto Styx 5.00 23
23 rows got
评论
后发表回复
达梦技术社区小助手
劳烦您这边把您的文章链接发到我们的公众号上，我们需要进行登记，发了就算投稿成功了哈！
发布于
2022/12/09 16:24
达梦在线服务平台
阿欧~内容不见啦😄~请您确认下草稿是否已提交哦✅✅
发布于
2022/12/07 21:58
作者
文章
阅读量
获赞
扫一扫
联系客服
联系客服
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1