我对 MongoDB 还很陌生，刚刚开始学习和使用它。我对查询有疑问。这是查询：
db.getCollection("user_plates").count(
{
"plates": {
$elemMatch: {
registerDate: {
$gt: new Date("2024-03-20T00:00:00.000Z"),
$lt: new Date("2024-03-21T00:00:00.000Z")
}
}
}
}
);
如您所知，如果查询在数组中找到至少一个满足两个条件的元素，它将返回该文档的所有元素。现在，我只需要该文档数组中的元素，该字段"registerDate"满足两个条件。我不想看到该文档数组的其他元素。我应该如何修改上述查询来实现这一点？
提前致谢
我有一张具有以下结构的表格：
create table test_table
(queuename number,
duration_sum number,
rating_sum number,
rating_avg number,
rating_cnt number
)
以下是示例数据：
insert into test_table (queuename, duration_sum, rating_sum, rating_avg,rating_cnt)
values (1000,50,40,60,70);
insert into test_table (queuename, duration_sum, rating_sum, rating_avg,rating_cnt)
values (1010,12,40,25,34);
insert into test_table (queuename, duration_sum, rating_sum, rating_avg,rating_cnt)
values (2000,50,34,60,23);
insert into test_table (queuename, duration_sum, rating_sum, rating_avg,rating_cnt)
values (3000,90,40,60,67);
commit;
我需要的是以下结果：
queuename 1000 1010 2000 3000
duration_sum 50 12 50 90
rating_sum 40 40 34 40
rating_avg 60 25 60 60
rating_cnt 70 34 23 67
我正在尝试使用pivot它，这就是我迄今为止所写的：
select *
from (
select
queuename,
'duration_sum' as measure, duration_sum as value from test_table
union all
select
queuename,
'rating_sum' as measure, rating_sum as value from test_table
union all
select
queuename,
'rating_avg' as measure, rating_avg as value from test_table
union all
select
queuename,
'rating_cnt' as measure, rating_cnt as value from test_table
)
pivot (
max(value)
for queuename in (1000 as "1000", 1010 as "1010", 2000 as "2000", 3000 as "3000")
)
order by measure;
这是一种更好的结果方法吗？我猜使用语句可能比使用pivoting有更好的方法。我应该说“queuename”是唯一的。pivotunion
提前致谢。
我有一张具有以下结构的表格：
create table TEST_TAB
(
activity_type CHAR(1),
tracking_code NUMBER,
eff_date DATE
)
该表的示例数据：
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('A', 1, to_date('01-11-2020', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('A', 1, to_date('02-01-2024', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('B', 2, to_date('01-08-2023', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('B', 2, to_date('02-08-2023', 'dd-mm-yyyy'));
insert into TEST_TAB (activity_type, tracking_code, eff_date)
values ('B', 2, to_date('03-08-2023', 'dd-mm-yyyy'));
这只是示例数据，原始表中的实际数据量接近 4 亿条记录。我需要做的是，对于每组activity_type, tracking_code，我需要保留具有最高“eff_date”的记录并删除其余记录。因此，对于，activity_type=A and tracking_code = 1我需要保留具有的记录eff_date = 1/2/2024并删除另一个记录。我现在有以下查询：
delete from test_tab
where rowid in (select rid
from (select rowid as rid,
row_number() over(partition by activity_type, tracking_code order by eff_date desc) as row_num
from test_tab
)
where row_num > 1
)
但是这似乎很慢。您能提出更好的解决方案吗？原始表按 eff_date 进行分区，并在其余两列上建立索引。另一点是，单个组中每条记录的 eff_date 之间可能相差一年以上。
提前致谢
我有一个具有以下结构的表：
create table student_info
(
item_number number,
st_firstname varchar2(50),
st_lastname varchar2(50),
st_score varchar2(50)
)
这是表的示例数据：
item_number | st_firstname | st_lastname | st_score
----------------------------------------------------------------------------
1 Ali|Reza|Pantea Hashemi|Nosrati|Yaghobi 10|20|20
2 Maryam|Ahmad Moghise|Majlesi 20|20
我需要有以下输出：
item_number | st_firstname | st_lastname | st_score
----------------------------------------------------------------------------
1 Ali Hashemi 10
1 Reza Nosrati 20
1 Pantea Yaghobi 20
2 Maryam Moghise 20
2 Ahmad Majlesi 20
我发现通过以下查询，我可以使用其中一列（即st_firstname）做我想做的事：
select distinct t.item_number,
trim(regexp_substr(t.st_firstname, '[^|]+', 1, level)) str
from student_info t
connect by instr(st_firstname, '|', 1, level - 1) > 0
order by t.item_number
问题是我不知道如何将其他列（st_lastname，st_lastname）添加到上述查询中。我想知道你是否可以在这里帮助我。
提前致谢
下面，我试图在查询中使用array我声明的数据，where clause但我收到以下错误：
declare
v_input varchar2(400) := '0,1,2,3,4';
type t_dep2custrel_type is table of number;
t_dep2cust_rel t_dep2custrel_type;
begin
select regexp_substr(v_input, '[^,]+', 1, level) zz
bulk collect
into t_dep2cust_rel
from dual
connect by regexp_substr(v_input, '[^,]+', 1, level) is not null;
insert into bb_tmp_natonalcode_accnumber
(identificationnumber)
select b.customer_num
from [sample_table] b
where b.dpst2cust_rel_cod in (select * from table(t_dep2cust_rel)); /* here I'm trying to use the array*/
commit;
end;
我想知道你是否可以帮我解决这个问题。
提前致谢
我有一个这种格式的输入参数'0,1,2,3,4'，我想做的是声明array一个可变大小的（我的意思是输入参数可能是这样'0,1'的，所以大小不固定），分开项目（0,1， 2,3,4) 并将它们存储在数字数组中。下面你可以看到我到目前为止所做的：
declare
v_input varchar2(400) := '0,1,2,3,4';
type t_dep2custrel_type is table of number;
t_dep2cust_rel t_dep2custrel_type;
begin
select REGEXP_SUBSTR(v_input, '[^,]+', 1, LEVEL)
into t_dep2cust_rel
from dual
CONNECT BY REGEXP_SUBSTR(v_input, '[^,]+', 1, LEVEL) IS NOT NULL;
end;
我收到此错误消息：
"Local collection types not allowed in SQL statements"
我想知道你是否可以帮我找到问题并告诉我是否有更好的方法来做到这一点。
提前致谢
我有一个具有以下结构的表：
create table item_test
(item_id varchar2(10),
item_row number)
示例数据如下，请注意Item_Row列中的数字从 1 开始，并以 1 递增为一个数字，没有任何间隙。我需要做的是([1-100] , [101-200] , [201-300] ,......)为每个Item_Id.
Item_Id Item_Row
------------ -----------
A 1
A 2
A ...
A ...
A 236 /* Item_Row starts from 1 and continues to 236 */
B 1
B ...
B ...
B 173 /* Item_Row starts from 1 and continues to 173 */
C 1
C 2
C ...
C ...
C 300 /* Item_Row starts from 1 and continues to 300 */
结果将如下所示：
Item_Id RowNum_From RowNum_From
------------ ----------- -----------
A 1 100
A 101 200
A 201 236
B 1 100
B 101 173
C 1 100
C 101 200
C 201 300
这有什么特定的功能吗？
我有一个具有以下结构和示例数据的表：
create table CUSTOMER_TEST
(
customer_num Number,
rel_one Number,
rel_two Number,
rel_three Number,
rel_four Number
)
Customer_num Rel_one Rel_two Rel_three Rel_four
--------------- ----------- ------------- -------------- --------------
1 7 12 1000 5
2 2 1 0 12
3 12 99 13 0
期望的结果是这样的：
Customer_num Rel_one Rel_two Rel_three Rel_four Relation_Code
--------------- ----------- ------------- -------------- -------------- ***************
1 7 12 1000 5 L07R12C99S05
2 2 1 0 12 L02R01C00S12
3 12 99 13 0 L12R99C13S00
我写的是这样的：
select customer_num,
rel_one ,
rel_two,
rel_three,
rel_four,
'L' ||
case
when rel_one > 99 then '99'
else lpad( rel_one,2,0) end ||
'R' ||
case
when rel_two > 99 then '99'
else lpad( rel_two,2,0) end ||
'C' ||
case
when rel_three > 99 then '99'
else lpad( rel_three,2,0) end ||
'S' ||
case
when rel_four > 99 then '99'
else lpad( rel_four,2,0) end as relation_code
from customer_test ;
有人有更好的主意吗？而不是使用这么多的案例陈述..
提前致谢
我有一张桌子，如下所示：
create table z_test_duration
( Days date,
Status char(8)
);
样本数据如下：
| 天 | 地位 |
|---|---|
| 2022 年 1 月 1 日 | 上 |
| 2022 年 1 月 2 日 | 上 |
| 2022 年 1 月 3 日 | 上 |
| 2022 年 1 月 4 日 | 离开 |
| 2022 年 1 月 5 日 | 上 |
| 2022 年 1 月 6 日 | 离开 |
| 2022 年 1 月 7 日 | 上 |
| 2022 年 1 月 8 日 | 上 |
| 2022 年 1 月 9 日 | 离开 |
想要的结果是这样的
| 时间到了 | OFF_DATE | COUNT_OF_ACTIVE_DAYS |
|---|---|---|
| 2022 年 1 月 1 日 | 2022 年 1 月 4 日 | 3 |
| 2022 年 1 月 5 日 | 2022 年 1 月 6 日 | 1 |
| 2022 年 1 月 7 日 | 2022 年 1 月 9 日 | 2 |
到目前为止，我的解决方案是：
select min(days) on_date,
off_day off_date,
off_day - min(days) cnt
from (select t1.off_day,
t1.prev_offday,
t2.days
from (
select t.days off_day,
nvl(lag(t.days, 1) over(order by t.days),convert(datetime, '1/1/2022') - 100) prev_offday
from z_test_duration t
where t.status = 'off'
) t1
inner join z_test_duration t2
on t2.days > t1.prev_offday
and t2.days < t1.off_day)
group by off_day;
我在想是否有更好的方法来解决这个问题，如果你能分享你解决这个问题的方法，我将不胜感激。
提前致谢。
我有一个具有以下结构的表：
create table Z_TEST_ONE
(
col_id NUMBER,
bed_amnt NUMBER,
bes_amnt NUMBER,
bop NUMBER
)
样本数据是这样的：
col_id bed_amnt bes_amnt bop
---------- ----------- ---------- -------
1 1000 0 20
1 5000 0 7
1 0 3000 10
1 0 6 14
2 1000 0 1
2 2000 0 2
2 0 1000 3
2 0 2000 4
现在我需要的是：对于每个col_id，最高的 bop 列bes_amnt必须等于最高的 bop bed_amnt。因此，执行查询后，结果必须如下所示：
col_id bed_amnt bes_amnt bop
---------- ----------- ---------- -------
1 1000 0 20
1 5000 0 7
1 0 3000 7 ---> changed from 10 to 7
1 0 6 14
2 1000 0 1
2 2000 0 2
2 0 1000 3
2 0 2000 2 ---> changed from 4 to 2
我为此写了一个查询，如下所示：
update z_test_one a
set a.bop =
(select t.bop /* bop for highest bed_amnt */
from z_test_one t
where t.bed_amnt = (select max(bed_amnt)
from z_test_one
where col_id = a.col_id
group by col_id))
where a.bes_amnt = (select max(bes_amnt)
from z_test_one
where col_id = a.col_id
group by col_id)
这个查询工作正常并给了我正确的结果，我想知道是否有更好的方法来编写所需的查询。
提前致谢
我有一个查询，如下所示：
select /*+gather_plan_statistics*/
*
from mi_dimcustomer t
where t.CUSTOMER_NUM = 321937
表中有一个Unique Index (IDX1_DIMCUSTOMER)onCustomer_Num列。我使用了/*+gather_plan_statistics*/提示并Dbms_xplan.display_cursor获得了真正的执行计划，这就是我所拥有的：
SQL_ID adj0b6drg6bjd, child number 0
-------------------------------------
select /*+gather_plan_statistics*/* from vmi_dimcustomer t where
t.CUSTOMER_NUM = 321937
Plan hash value: 3784660444
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (100)| 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| MI_DIMCUSTOMER | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 |
|* 2 | INDEX UNIQUE SCAN | IDX1_DIMCUSTOMER | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CUSTOMER_NUM"=321937)
我的问题是，Operation-2 , Index unique scan.据我所知，发生的Index scan只是扫描/搜索Index Page，检索所需的 Rowid，然后使用这些 Rowid 访问表中的正确位置（这是 op-1）。我不期望操作-2（它只是扫描索引页）以生成任何行！那么为什么我们在 op-2 的执行计划中看到 [A-rows]=1 呢？这个数字代表什么？
我的猜测是它将等于从 index page 返回的 ROWID 的数量。
提前致谢
我有一个查询，如下所示：
with cte as
(select customer_num
from vmi_segment_customer_relation
where effective_date = to_date('12/30/2021', 'mm/dd/yyyy')
and segment_id = 10000000592
)
select
t.customer_num,
cust_first_name,
cust_last_name,
cust_type_desc
from vmi_factcustomer t
join cte f
on t.customer_num = f.customer_num
and t.effective_date = to_date('12/30/2021', 'mm/dd/yyyy')
join vmi_dimcustomer d
on t.customer_num = d.customer_num;
如您所见，此查询中有三个表
1) vmi_segment_customer_relation、索引："IDX1_SEGMENT"在"segment_id" 列上。
2) vmi_factcustomer、索引："IDX1_F"在"customer_num" 列上。
3) vmi_dimcustomer、索引："IDX_CUSTNUM"在"customer_num"列上。
所有表statistics都是最新的，并且没有过时的统计信息。我execution plan使用这个提示得到了这个查询的真实信息/*+gather_plan_statistics*/，你可以看到这里是计划：
我对计划有一些疑问：
我希望在operation-10（operation-11op-10 是 op-11 的子项）下，因为'IDX_CUSTNUM'索引是针对'MI_DIMCUSTOMER'table 的！看看op-5 and op-6例子。或者op-8 and op-9，我希望 op-10 和 op-11 完全像这两个一样，但不知道为什么不是！
另一个问题是，查询中有两个连接，为什么我们Nested loop joins在计划中看到三个？每个嵌套循环的作用是什么？
提前致谢
我刚刚熟悉了"qb_name" (query block name)Oracle 中的提示，下面您可以看到在查询中使用此提示的示例：
select
/*+
qb_name(main)
*/
ord.id,
ord.valuation,
ord.status,
(select /*+ qb_name(company) */ max(com.name) from companies com where com.id = ord.id_company) company,
(select /*+ qb_name(product) */ max(prd1.name) from products prd1 where prd1.id = orl.id_product) product,
orl.quantity
from
orders ord,
order_lines orl
where
ord.date_placed > trunc(sysdate) - 7
and orl.id_ord = ord.id
and orl.id_product in (
select /*+ qb_name(class) */
prd2.id
from products prd2
where prd2.class = 'Group25'
)
我已经搜索过它，但这些文章并不是那么实用，所以我决定在这里询问这个提示。我的问题是：
performance吗？提前致谢
我有一张包含大量数据（近 1500 万）及以下结构的表格。
create table test
(a int,--> /* There is a normal index on this column */
b int,
<other columns>)
有一个从此表中选择的查询，where 子句中的条件之一是：
where a!=1 or (a=1 and b!=0) /* The original condition */
查询非常慢，我认为这种糟糕的性能大部分可能是因为逻辑运算符使用不当。我已经改变了条件，如下所示：
where not (a=1 and b=0) /* The edited version*/
并且性能发生了巨大变化！我需要确定的是这两个条件完全相同，所以我不会错过任何数据。我想知道您是否可以帮助我，并告诉我您是否有更好的替代方案。
如果您知道任何关于正确使用逻辑操作以及方式/顺序优化器处理它们的文章，请分享链接。
提前致谢
我有一个具有以下结构和数据的表：
create table PTEST
(
col_name VARCHAR(50),
col_value VARCHAR(50)
)
COL_NAME COL_VALUE
-----------------------
first apple
first banana
second apple
second banana
second orange
third apple
third banana
**) 我要做的是将col_value列中的每个值分为两类：[常见，不常见]
**)'Common'如果一个值出现在每个中，则考虑一个值col_name，因此apple很常见，因为它出现在 中col_name = first and col_name = second and col_name = third。对于banana. Orange并不常见，因为它只是出现在col_name = second.
所需的输出将是这样的：
COL_NAME COL_VALUE STATUS
---------------------------------
first apple Common
first banana Common
second banana Common
second apple Common
second orange Not common
third apple Common
third banana Common
我为此写的查询是：
select col_name,
col_value,
case
when count_col = count_val then
'Common'
else
'Not common'
end STATUS
from (select t.col_name,
count(distinct t.col_name) over() count_col,
t.col_value,
count(t.col_value) over(partition by t.col_value) count_val
from PTEST t)
我想知道是否有更好的方法来做到这一点。
提前致谢
我有一个具有以下结构的示例表：
create table mi_dimcustomer
(customer_num number(10),
<other columns> <data types>)
并且有一个unique indexon 列customer_num。我试图hint the optimizer不像这样使用这个索引（只是为了练习）：
select /*+gather_plan_statistics*/ /*+ no_index(t idx1_dimcustomer) */
*
from mi_dimcustomer t
where t.customer_num = 12;
但是在执行计划中，我可以看到优化器还在使用索引！这是我捕获执行计划的方式：
Step-1)
select sql_id, child_number, sql_text
from v$sql
where sql_text like '%where t.CUSTOMER_NUM = 12%';
Step-2)
select *
from table(dbms_xplan.display_cursor('2qataxp9mahpj',
'0',
'ALLSTATS LAST +COST +OUTLINE'))
您可以在下面看到执行计划：
SQL_ID 2qataxp9mahpj, child number 0
-------------------------------------
select /*+gather_plan_statistics*//*+ NO_INDEX(t idx1_dimcustomer) */ *
from mi_dimcustomer t where t.CUSTOMER_NUM = 12
Plan hash value: 3784660444
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | Cost (%CPU)| A-Rows | A-Time | Buffers |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 2 (100)| 1 |00:00:00.01 | 3 |
| 1 | TABLE ACCESS BY INDEX ROWID| MI_DIMCUSTOMER | 1 | 1 | 2 (0)| 1 |00:00:00.01 | 3 |
|* 2 | INDEX UNIQUE SCAN | IDX1_DIMCUSTOMER | 1 | 1 | 1 (0)| 1 |00:00:00.01 | 2 |
-----------------------------------------------------------------------------------------------------------------------
为什么会这样？
提前致谢
我正在尝试捕获execution plan我的查询，PL/SQL developer如下所示：
select *
from vmi_dimcustomer t1
inner join vmi_factcustomer t2
on t1.customer_num = t2.customer_num ;
Select plan_table_output from table(dbms_xplan.display_cursor(null,null,'basic'));
但我收到了这张纸条：
SQL_ID 9m7787camwh4m, child number 0
begin :id := sys.dbms_transaction.local_transaction_id; end;
NOTE: cannot fetch plan for SQL_ID: 9m7787camwh4m, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_plan)
我在这里做错了什么？我在这里搜索，我得到的答案是"set serveroutput off"。把我不能在 PL/SQL 开发人员上做到这一点。
提前致谢。
我正在Explain plan and Execution planOracle 中学习，根据我目前所读到的内容，解释的计划可能与语句执行期间使用的实际计划不同。所以我认为执行计划比解释计划更有用optimization purposes。
我的问题 ：
一个Sql语句，你通常如何生成执行计划？在阅读了不同的文章后，我现在有点困惑，因为在每篇文章中都引入了不同的方法！例如，我遇到了这个：
explain plan
set statement_id = 'ex_plan1' for
select phone_number
from employee
where phone_number like '650%';
select PLAN_TABLE_OUTPUT
from table (DBMS_XPLAN.DISPLAY(STATEMENT_ID=>'ex_plan1'));
这是correct way为了制定执行计划（不是解释计划）吗？
拥有正确执行计划的其他方法是什么？（that optimizer选择执行语句的方法）。
提前致谢
我创建了一个这样的序列：
CREATE SEQUENCE seq_test2
MINVALUE 0
MAXVALUE 999999999999999999999999999
START WITH 0
INCREMENT BY 1;
这两个查询都向我显示数字 0：
select SEQ_TEST2.nextval from DUAL;
select SEQ_TEST2.currval from DUAL;
我有一张桌子，你可以在这里看到：
create table STUDENT
(
st_id NUMBER,
first_name VARCHAR2(150),
last_name VARCHAR2(150)
)
执行下面的查询后，
select t.st_id , nvl(t.st_id , seq_test2.nextval) as seq
from STUDENT t
当 column 中没有 null 时st_id，我得到的结果是：
st_id seq
---------------
1 1
2 2
3 3
4 4
5 5
当我插入列为空的行时st_id，执行上面的查询后，结果如下：
st_id seq
---------------
1 1
2 2
3 3
4 4
5 5
null 10
如果我再次执行它，我会看到：
st_id seq
---------------
1 1
2 2
3 3
4 4
5 5
null 16
好像即使列st_id有数据，这部分NVL function(seq_test2.nextval) 也会被执行！！！为什么会这样？
提前致谢
维度建模中的一种事实表是Accumulating Snapshot fact Table. 如果你觉得需要复习一下这个类型的含义和定义，可以看看这篇文章：
https ://www.holistics.io/blog/the-three-types-of-fact-tables/ 。
想象一下，我们有一个累积事实表，其中包含三个重要的日期列
'Order_Datekey , Manufacturing_Datekey , Ship_Datekey'
第一次插入表时，列没有数据Manufacturing_Datekey and Ship_Datekey'。我们只知道订购产品的日期（Order_date）。我需要知道的是我们如何处理 Date 列的空值？我在 Kimbal 书中读到的重点是在外键列中应避免 null。
'Referential
integrity is violated if you put a null in a fact table column declared as a foreign key
to a dimension table'
另一方面，我们起初对这两个日期列没有任何价值。你有什么建议？
提前致谢