Sfrgttk

Why do we divide Permutations to get to Combinations?

How can a kingdom keep the secret of a missing monarch from the public?

Discouraging missile alpha strikes

Fired for using Stack Exchange. What did I do wrong?

Is Screenshot Time-tracking Common?

How do I write a maintainable, fast, compile-time bit-mask in C++?

Is it possible to detect 100% of SQLi with a simple regex?

Sing Baby Shark

Why did some CPUs use two Read/Write lines, and others just one?

How to write painful torture scenes without being over-the-top

What does "don't have a baby" imply or mean in this sentence?

Manager has noticed coworker's excessive breaks. Should I warn him?

Someone wants me to use my credit card at a card-only gas/petrol pump in return for cash

Relation between roots and coefficients - manipulation of identities

Why is ra lower than re while la is higher than le?

Have any astronauts or cosmonauts died in space?

Run a command that requires sudo after a time has passed

How many copper coins fit inside a cubic foot?

Are encryption algorithms with fixed-point free permutations inherently flawed?

React Native EXPO Apple upload fail

Why is quixotic not Quixotic (a proper adjective)?

Does the kobold player race feature, Pack Tactics, give ranged attacks advantage?

How to know if I am a 'Real Developer'

Buying a "Used" Router

Single-column user defined variable based on a comparison

Selecting minimum value using a subqueryInsert Row With Foreign Key Equal to ID - Circular ReferenceMySQL update one table from another joined many-to-manyMySQL code to show specific rows based on another…?Alfresco DB - Most perfomance effective way to replace valuesSQL - Restricted view for transient (stateless) userHow to calculate total distance by datetimeMySql - select from main table with a lot of connected tablesHow is it possible that a key of char+bigint has better performance than key of bigint+bigint?Unable to fix ROW size too large even with innodb_file_per_table

0

I have a database with over 300k lines and each row is a separate timed event. I have a column of system spec values for error, followed by a stochastic draw on an actual error for that event. I can change the confidence level for the actual error by multiplying the actual error by a constant.

I have tried this query and while it returns a 1 in each error code, what I am looking for is a way to develop a user defined variable called “myerror” that is filled by each rows error * the constant.

sample query statements:

((select(actual_error * “constant”) <= lowest_threshold) as level_1_error,

((select(actual_error * “constant”)  between this and that) as level_2_error,

((select(actual_error * “constant”)  between higher_this and higher_that) as level_3_error,

…

…

…

((select(actual_error * “constant”) >= highest_threshold) as max_error,

What I’m trying for is something like:

Case when (select(actual_error * “constant”) <= lowest_threshold  set @user_error  =  “level_1_error”

          when (select(actual_error * “constant”)  between this and that) set @user_error  =  “level_2_error”

but, alas…..can’t seem to get it to work…..I’ve tried putting the set statement in a sub-query and just using a sub-query to select a different error level also…..

help bitte sehr.

mysql

share|improve this question

edited 2 mins ago

Randolph West

2,649215

asked 11 hours ago

JimJim

1

New contributor

Jim is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  SHOW CREATE TABLE {x} for the table structure, some sample data, and expected output. You description is quite hard to follow.
  
  – danblack
  10 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  each row is a separate timed event. Does this mean that there exists some timestamp field in table's structure, and it is unique?
  
  – Akina
  4 hours ago
  

  
  
  
  

add a comment | 

0

I have a database with over 300k lines and each row is a separate timed event. I have a column of system spec values for error, followed by a stochastic draw on an actual error for that event. I can change the confidence level for the actual error by multiplying the actual error by a constant.

I have tried this query and while it returns a 1 in each error code, what I am looking for is a way to develop a user defined variable called “myerror” that is filled by each rows error * the constant.

sample query statements:

((select(actual_error * “constant”) <= lowest_threshold) as level_1_error,

((select(actual_error * “constant”)  between this and that) as level_2_error,

((select(actual_error * “constant”)  between higher_this and higher_that) as level_3_error,

…

…

…

((select(actual_error * “constant”) >= highest_threshold) as max_error,

What I’m trying for is something like:

Case when (select(actual_error * “constant”) <= lowest_threshold  set @user_error  =  “level_1_error”

          when (select(actual_error * “constant”)  between this and that) set @user_error  =  “level_2_error”

but, alas…..can’t seem to get it to work…..I’ve tried putting the set statement in a sub-query and just using a sub-query to select a different error level also…..

help bitte sehr.

mysql

share|improve this question

edited 2 mins ago

Randolph West

2,649215

asked 11 hours ago

JimJim

1

New contributor

Jim is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  SHOW CREATE TABLE {x} for the table structure, some sample data, and expected output. You description is quite hard to follow.
  
  – danblack
  10 hours ago
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  each row is a separate timed event. Does this mean that there exists some timestamp field in table's structure, and it is unique?
  
  – Akina
  4 hours ago
  

  
  
  
  

add a comment | 

I have a database with over 300k lines and each row is a separate timed event. I have a column of system spec values for error, followed by a stochastic draw on an actual error for that event. I can change the confidence level for the actual error by multiplying the actual error by a constant.

I have tried this query and while it returns a 1 in each error code, what I am looking for is a way to develop a user defined variable called “myerror” that is filled by each rows error * the constant.

sample query statements:

((select(actual_error * “constant”) <= lowest_threshold) as level_1_error,

((select(actual_error * “constant”)  between this and that) as level_2_error,

((select(actual_error * “constant”)  between higher_this and higher_that) as level_3_error,

…

…

…

((select(actual_error * “constant”) >= highest_threshold) as max_error,

What I’m trying for is something like:

Case when (select(actual_error * “constant”) <= lowest_threshold  set @user_error  =  “level_1_error”

          when (select(actual_error * “constant”)  between this and that) set @user_error  =  “level_2_error”

but, alas…..can’t seem to get it to work…..I’ve tried putting the set statement in a sub-query and just using a sub-query to select a different error level also…..

help bitte sehr.

mysql

share|improve this question

edited 2 mins ago

Randolph West

2,649215

asked 11 hours ago

JimJim

1

New contributor

Jim is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

((select(actual_error * “constant”) <= lowest_threshold) as level_1_error,

((select(actual_error * “constant”)  between this and that) as level_2_error,

((select(actual_error * “constant”)  between higher_this and higher_that) as level_3_error,

…

…

…

((select(actual_error * “constant”) >= highest_threshold) as max_error,

Case when (select(actual_error * “constant”) <= lowest_threshold  set @user_error  =  “level_1_error”

          when (select(actual_error * “constant”)  between this and that) set @user_error  =  “level_2_error”

share|improve this question

edited 2 mins ago

Randolph West

2,649215

asked 11 hours ago

JimJim

1

New contributor

Jim is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share|improve this question

edited 2 mins ago

Randolph West

2,649215

asked 11 hours ago

JimJim

1

New contributor

Jim is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

edited 2 mins ago

Randolph West

2,649215

edited 2 mins ago

Randolph West

2,649215

asked 11 hours ago

JimJim

1

New contributor

Jim is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 11 hours ago

JimJim

1

1 Answer
1

active

oldest

votes

0

I'd recommend you to create static, temporary or dynamic table which contains the adjacent border ranges for each error level normalized with “constant” value to avoid additional calculations (schema):

CREATE TABLE error_level_ranges ( level, lower_bound, upper_bound )

AS 

SELECT 1,-1,2     -- 2 as "lowest_threshold"/constant,

                  -- assume -1 is below min possible value of actual_error

UNION ALL

SELECT 2,2,10     -- 10 as "that"/constans, assume "this"="lowest_threshold"

UNION ALL

SELECT 3,10,25    -- 25 as "higher_that"/constant, assume "higher_this"="that"

-- ... 

UNION ALL

SELECT 99,100,999 -- 100 as "highest_threshold"/constant, assume levels count < 99

                  -- and 999 is over max possible value of actual_error

and then

SELECT t1.*, t2.level

FROM error_events t1, error_level_ranges t2

WHERE t1.actual_error >= t2.lower_bound

  AND t1.actual_error < t2.upper_bound

/* or alternatively, dependent by a real logic

WHERE t1.actual_error > t2.lower_bound

  AND t1.actual_error <= t2.upper_bound */

If you have a lot of error categorization schemes with different level counts and their borders, you must add a field (named similar to categorizing_scheme) into error_level_ranges table and proper additional expression like AND t2.categorizing_scheme=@current_scheme to conditions section.

share|improve this answer

edited 3 hours ago

answered 3 hours ago

AkinaAkina

4,0361311

add a comment | 

Your Answer

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});

Jim is a new contributor. Be nice, and check out our Code of Conduct.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f230323%2fsingle-column-user-defined-variable-based-on-a-comparison%23new-answer', 'question_page');
}
);

Post as a guest

Name

Email

Required, but never shown

0

I'd recommend you to create static, temporary or dynamic table which contains the adjacent border ranges for each error level normalized with “constant” value to avoid additional calculations (schema):

CREATE TABLE error_level_ranges ( level, lower_bound, upper_bound )

AS 

SELECT 1,-1,2     -- 2 as "lowest_threshold"/constant,

                  -- assume -1 is below min possible value of actual_error

UNION ALL

SELECT 2,2,10     -- 10 as "that"/constans, assume "this"="lowest_threshold"

UNION ALL

SELECT 3,10,25    -- 25 as "higher_that"/constant, assume "higher_this"="that"

-- ... 

UNION ALL

SELECT 99,100,999 -- 100 as "highest_threshold"/constant, assume levels count < 99

                  -- and 999 is over max possible value of actual_error

and then

SELECT t1.*, t2.level

FROM error_events t1, error_level_ranges t2

WHERE t1.actual_error >= t2.lower_bound

  AND t1.actual_error < t2.upper_bound

/* or alternatively, dependent by a real logic

WHERE t1.actual_error > t2.lower_bound

  AND t1.actual_error <= t2.upper_bound */

If you have a lot of error categorization schemes with different level counts and their borders, you must add a field (named similar to categorizing_scheme) into error_level_ranges table and proper additional expression like AND t2.categorizing_scheme=@current_scheme to conditions section.

share|improve this answer

edited 3 hours ago

answered 3 hours ago

AkinaAkina

4,0361311

add a comment | 

0

I'd recommend you to create static, temporary or dynamic table which contains the adjacent border ranges for each error level normalized with “constant” value to avoid additional calculations (schema):

CREATE TABLE error_level_ranges ( level, lower_bound, upper_bound )

AS 

SELECT 1,-1,2     -- 2 as "lowest_threshold"/constant,

                  -- assume -1 is below min possible value of actual_error

UNION ALL

SELECT 2,2,10     -- 10 as "that"/constans, assume "this"="lowest_threshold"

UNION ALL

SELECT 3,10,25    -- 25 as "higher_that"/constant, assume "higher_this"="that"

-- ... 

UNION ALL

SELECT 99,100,999 -- 100 as "highest_threshold"/constant, assume levels count < 99

                  -- and 999 is over max possible value of actual_error

and then

SELECT t1.*, t2.level

FROM error_events t1, error_level_ranges t2

WHERE t1.actual_error >= t2.lower_bound

  AND t1.actual_error < t2.upper_bound

/* or alternatively, dependent by a real logic

WHERE t1.actual_error > t2.lower_bound

  AND t1.actual_error <= t2.upper_bound */

If you have a lot of error categorization schemes with different level counts and their borders, you must add a field (named similar to categorizing_scheme) into error_level_ranges table and proper additional expression like AND t2.categorizing_scheme=@current_scheme to conditions section.

share|improve this answer

edited 3 hours ago

answered 3 hours ago

AkinaAkina

4,0361311

add a comment | 

I'd recommend you to create static, temporary or dynamic table which contains the adjacent border ranges for each error level normalized with “constant” value to avoid additional calculations (schema):

CREATE TABLE error_level_ranges ( level, lower_bound, upper_bound )

AS 

SELECT 1,-1,2     -- 2 as "lowest_threshold"/constant,

                  -- assume -1 is below min possible value of actual_error

UNION ALL

SELECT 2,2,10     -- 10 as "that"/constans, assume "this"="lowest_threshold"

UNION ALL

SELECT 3,10,25    -- 25 as "higher_that"/constant, assume "higher_this"="that"

-- ... 

UNION ALL

SELECT 99,100,999 -- 100 as "highest_threshold"/constant, assume levels count < 99

                  -- and 999 is over max possible value of actual_error

and then

SELECT t1.*, t2.level

FROM error_events t1, error_level_ranges t2

WHERE t1.actual_error >= t2.lower_bound

  AND t1.actual_error < t2.upper_bound

/* or alternatively, dependent by a real logic

WHERE t1.actual_error > t2.lower_bound

  AND t1.actual_error <= t2.upper_bound */

If you have a lot of error categorization schemes with different level counts and their borders, you must add a field (named similar to categorizing_scheme) into error_level_ranges table and proper additional expression like AND t2.categorizing_scheme=@current_scheme to conditions section.

share|improve this answer

edited 3 hours ago

answered 3 hours ago

AkinaAkina

4,0361311

CREATE TABLE error_level_ranges ( level, lower_bound, upper_bound )

AS 

SELECT 1,-1,2     -- 2 as "lowest_threshold"/constant,

                  -- assume -1 is below min possible value of actual_error

UNION ALL

SELECT 2,2,10     -- 10 as "that"/constans, assume "this"="lowest_threshold"

UNION ALL

SELECT 3,10,25    -- 25 as "higher_that"/constant, assume "higher_this"="that"

-- ... 

UNION ALL

SELECT 99,100,999 -- 100 as "highest_threshold"/constant, assume levels count < 99

                  -- and 999 is over max possible value of actual_error

SELECT t1.*, t2.level

FROM error_events t1, error_level_ranges t2

WHERE t1.actual_error >= t2.lower_bound

  AND t1.actual_error < t2.upper_bound

/* or alternatively, dependent by a real logic

WHERE t1.actual_error > t2.lower_bound

  AND t1.actual_error <= t2.upper_bound */

share|improve this answer

edited 3 hours ago

answered 3 hours ago

AkinaAkina

4,0361311

answered 3 hours ago

AkinaAkina

4,0361311

answered 3 hours ago

AkinaAkina

4,0361311